Embedding vs foreign table in one-to-one relations

database-design

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?

Best Answer

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

Related Question