All data in one table or one to one relationship?

[azapovjednik]

Hello all,

I need to make some database... and lets say there is a student table, and the student table has many columns, a lot of columns.... they are all unique to the primary key, so dont worry about the normal forms...

what I am in a doubt is: there is some data, that could be put in another table, but it should be a one-to-one relationship!
because, one student can have only one dossier (for instance: sign_up_date, student_status, etc)

so, my question is:
- should all the data be put into one table called students?

• or should I separate the data into two tables students and student_dossier? In this case, student_dossier *table should have one primary key named student_id, just like in *students table

I am a bit confused, so I hope you could help me to understand it... Thank you very much in advance

[Reverend Jim]

You have to strike a balance between normalization and usability. For a student table, you should keep basic information like

Name
Address
Telephone
StudentID (which will be used as a foreign key in other tables)
Status

In one table. This is information that is used to uniquely identify a student. Academic information such as courses, grades, etc. belong in a separate table. These other tables will refer back to the student table via the StndentID.

[azapovjednik]

I am aware of the need to put separate data into separate table.... like grades, etc...
but what about one to one relationship? in what instance do we use it? is it like having all the data in one table...

I am looking in Access 2007, in order to create one to one relationship, two tables need to have one and only and the same primary key, like StudentID

so I wonder, does separating data in that way only help to alleviate the burden of one table? Is that it? Is that the purpose of having two tables with the same ID field?

Is the database gonna be slow that way?

I hope you can help me understand that concept better, thanks again.

[Reverend Jim]

Tables do not need to be restricted to a one to one relationship. Databases would not be very useful if this was the case. For example, one student can take many courses. In that case you might have (as an example)

StudentsTable
-------------
StudentID (PK)
LastName
FirstName
.
.
.

CourseTable
-----------
CourseID (PK)
CourseName
.
.
.

StudentCourseTable
------------------
StudentID (FK)
CourseID (FK)
Grade
.
.
.

Because you need to have a primary key to uniquely ID a record in the StudentCourse table, the primary key (PK) is a compound key consisting of the StudentID and CourseID which are also foreign keys (PK) because they link back to other tables. In real life it gets much more compliciated because you would need at least two more tables for courses. The primary would detail each course but you would need another one-many because each course would be offered in multiple slots and multiple terms, and yet another to specify the prerequisites.

[azapovjednik]

is my English that bad? All I was asking was: one to one relationships as opposed to all the data in one table, instead of two tables.

[nauticalmac]

I think it would depend on how you would access the data under normal use. Would all your queries need to access all the data? Or might there be a subset that is accessed more frequently where having it in a separate table may marginally improve performance?
I think splitting the data into two tables is something I would only consider if the number of records and the number of fields was very large.

[Reverend Jim]

is my English that bad? All I was asking was: one to one relationships as opposed to all the data in one table, instead of two tables.

I suppose I could have just answered "it depends on the data" but that really wouldn't have been useful.

[azapovjednik]

I see, thanks nauticalmac.... that makes sense then..... since there are many fields, and also this data in a separate table would be considered as one group... then it makes sense. Thank you.