CODEWITHSUNDEEP
databasems-accessdatabase-design
Saverio
Saverio

Reputation: 99

Separate tables for 1-1 relationship

I'm creating an Access database to hold student internship information. The issue I'm having is I have three tables that have a one and only one relationship with the internship table (Assignment, Supervisor Evaluation, and Student Evaluation).

Since Access doesn't allow a table to have more than one auto generated number, I can't let the internship table create the ID number for each of the three tables. So, I'm not sure how to make it so when we enter data into these tables forms, I can assign it specifically to an internship. Any advice?

enter image description here

Upvotes: 0

Views: 613

Answers (1)

TAM
TAM

Reputation: 1741

1-1 relationships always smell like they should be merged into one table. This is particularly so if they are actually 1-1 and shouldn't be 1-0,1. In the latter case, if the dependent information can be missing and will be missing in a majority of cases, it might be helpful to separate it away into a table of its own. But even this can be expressed by giving null values to certain attributes.

Now if, for some reason, you insist on those 4 tables, there are two ways to go for the primary keys. One is, for the dependent tables, not to declare the primary key as auto-generated, but just as a number, and to assign to it the autogenerated value of the Internship record. Another is to auto-generate a primary key for each of the dependent tables, and have a foreign key in the Intership table for each of them. As I consider the entire construct of those dependent tables as unnecessarily complicated, I can't give a recommendation on which of these ways to prefer.

There is another concern I have about your data model. Your tables have those attributes like answer1, answer2, ... Now if you have a small fixed amount of those attributes, this might be okay. But could you have a larger set of fixed questions, maybe for each type of internship, that might vary dynamically and can't just be expressed by a fixed column structure? In that case you would need something like

Question(id, text)
Internship(id, ...)
Answer(id, internship_id, question_id, student_answer, supervisor_evaluation)

So your cardinalities would be

Internship 1-----0,n Answer 0,n------1 Question

Same for the other details of the internship.

Upvotes: 1

Related Questions