Should I store the parent and child in a row or only the child

Question

Let's say I'm building a car rentals application, and at a point I have to show all available cars.

In the DB, I have the following tables:

• Brand
• Model
• Cars

In Model, there is a FK to Brand, let's say Brand_id

So my question is:

Cars table should have Brand and Model columns? Or only a Model column given that I could get the Brand from the Model column?

Answer 1

It should have both FK references in my opinion. Dependant on the relationships of these tables you'd also want to avoid a possible fan trap.

For example
A --- (1:N) --- B --- (1:N) --- C

Another example can be for instance:
A is any dimension (for instance DATES)
B is a table with shipping information and data is on a package level
C is a detailed table with an info on a package items level

Make sense?

Answer 2

The cars table should have a key into model only. You'd never have a Ford Corvette or a Chevrolet Mustang, nor would you have the same car made my more than one manufacturer.

If you need the brand as well, you'd join all three tables, on model.id = cars.model and model.brand = brand.id.

Answer 3

Basic normal form requires that the car table NOT include a column which is dependent on another (non PK) column in the car table. Since brand is dependent on model, it should not be in the car table.

Put simply, you're storing an awful lot of redundant information if you repeat brand for every car. You can access that info easily by JOINing the model table into a query. And what happens if you have some "car" rows that claim a model belongs to one brand while others claim it belongs to another? That's not only wrong, in the real world it's impossible and therefore it should not be possible in your database.

Answer 4

It depends I think. If you query car brand from your cars table A LOT, and I mean A LOT, (That means you want to know the brand of the car without knowing the model therefore you join car and brand tables without using model) you may consider adding brandid to your cars table so you can join Cars and Brand tables without using Model table.
If you have so many entries in your cars table (Again, I mean Soo Many), (and I doubt it) you may consider reducing the table size by deleting brandid and just use Model table to access to brands.
But that's not the only case. If you store brandid in your cars table, you wouldn't comply basic database normalization rules. What I have mentioned affects performance when you have 10,000+ rows in tables. So I'm pretty sure you're ok in performance in both approaches.
I'd define just a modelid FK in my cars table since there's no need to have brandid when you have modelid and you're not worried about the performance.