Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm currently taking a university course about databases.

Consider the following modeling scenario where a car exists only in the context of its owning student:

Student (0,1) <---> (1,1) Car

As I'm reviewing the lecture material, there are two transformations available for this:

1) Put car into it's own entity type and create a student-car relation (store car's primary key in student).

2) "Embed" car's attributes into student entity type with a boolean flag has_car.

With 1) the student table and over all database size is smaller because only a NULL is saved to car_id. However, getting student with a car requires the car's attributes to be fetched from a foreign table.

With 2) there's no need for reaching into foreign tables. However, the database and student table is now bigger because we are saving all of the car's attributes even for student's with no car.

If there are very few students with a car, 1) makes sense.

If there are only very few attributes we store about car, 2) might make sense.

In a more general situation, how does one decide which transformation to use? Could car have a huge amount of attributes and 2) make still sense? Is it possible to calculate the correct solution by calculating the space the car's attributes take?

share|improve this question

3 Answers 3

Option 1 is close...

However:

If a student can own zero to many cars:

  • you'd have ownerstudentid in the car table
  • car is a child of student

If a car can exist without a student, I'd consider a many-many table carstudent with a constraint on carid to allow zero or one owning student

Note:

This is where tools like NORMA come in useful to capture these relationships and constraints in plain English. See Object Role Modelling

share|improve this answer
    
And then there's the case of students sharing a car. (probably most common with siblings, in the college situation, but also possibly for a pair of married grad students) –  Joe Feb 5 '11 at 20:45
    
These options may not need to be in the design today, but by starting out with a design like this future changes will be considerably easier without adding undo difficulty on today's coding. –  Leigh Riffel Feb 6 '11 at 3:41

I'm not going to claim that there's ever only one "correct" way to handle things -- but what you're proposing in #2 would be considered a violation of Third Normal Form.

Basically, the issue is that the attributes of the car are attribute of the car and not the student. Ownership of the car is an attribute of the student, which would be your foreign key to the car table.

...

Now, do you need to normalize? Maybe not. If you want to actually track cars, such as if a given car were sold to a different student, you'd obviously need to uniquely identify each vehicle, and so it'd make sense to normalize it.

If you're starting to get too many tables, and you have to do a lot of joins for your typical queries, you might keep your data denormalized for tuning ... but as you mention, some databases (but not all) will have to allocate just as much space for the record having lots of null values as it would've if there were values in there, so normalization typically reduces the storage size.

share|improve this answer

Option 1 would be the correct relational design.

share|improve this answer
    
I would think it would make more sense to put studentid in the car table than to put carid (with a null) in the student table. Joining against the table would return no records for the car if no relation existed and would take less space overall. So I don't think that Option 1 is the correct relational design but that it is the more correct relational design. –  jcolebrand Feb 5 '11 at 20:10
    
According to the rules of data normalization if there is a second object which has attributes (the student being the first object, and the car being the second object) then the second object has to go into another table. Then it is up to business rules to define if you need a one to many or a many to many relationship between the two objects (students and cars). –  mrdenny Feb 5 '11 at 22:12
    
I must not have been clear @mrdenny. I meant, the car would get the FK field of student, not the student getting a possible FK field (or alternately null) of the car, according to proper normalization rules. –  jcolebrand Feb 6 '11 at 20:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.