Database Design w/ Foreign Keys Question

Question

I'm trying to use foreign keys properly to maintain data integrity. I'm not really a database guy so I'm wondering if there is some general design principle I don't know about. Here's an example of what I'm trying to do:

Say you want to build a database of vehicles with Type (car, truck, etc.), Make, and Model. A user has to input at least the Type, but the Make and Model are optional (if Model is given, then Make is required). My first idea is to set up the database as such:

Type:
-id (PK)
-description

Make:
-id (PK)
-type_id (FK references Type:id)
-description

Model:
-id (PK)
-make_id (FK references Make:id)
-description

Vechicle:
-id (PK)
-type_id (FK references Type:id)
-make_id (FK references Make:id)
-model_id (FK references Model:id)

How would you setup the FKs for Vehicle to ensure that the Type, Make, and Model all match up? For example, how would you prevent a vehicle having (Type:Motorcyle, Make:Ford, Model:Civic)? Each of those would be valid FKs, but they don't maintain the relationships shown through the other tables' FKs.

Also, because Model isn't required, I can't just store the model_id FK and work backwards from it.

I'm not tied to the database design at all, so I'm open to the possibility of having to change the way the tables are set up. Any ideas?

P.S. - I'm using mysql if anyone's interested, but this is more of a general question about databases.

Edit (Clarifications):

-type_id and make_id are needed in the vehicle table unless there is some way to figure those out in the case that model_id is null;

-the relationships between type_id, make_id, and model_id need to be maintained.

Answer 1

Your design is fine for data integrity, it will be the job of your application to maintain that a Vehicle must be made up of Makes from a particular Type and Models of a particular Make.

If you want to maintain vehicle type/make/model integrity in the database you could add a check constraint to your Vehicle table that makes sure the Vehicle's make's type id equals the provided type id. And if the model id is not null, make sure it's make id is the same as the make id provided.

Answer 2

Like this:

Type:

• id (PK)
• description

Make:

• id (PK)
• type_id (FK references Type:id, not null)
• description

Model:

• id (PK)
• make_id (FK references Make:id, not null)
• description

Vechicle:

• id (PK)
• model_id (FK references Model:id)

Basically don't double reference make and type from vehicle as well. You'll run into problems if you do that. You can get the make and type from the model of the vehicle (if defined). Model must have make. Make must have type.

Think about that for a second: if vehicle has a given model but vehicle and model both have a make, those values can be different. This kind of inconsistency can develop because of information redundancy. You want to avoid that generally.

If you need to figure out the make and type of a vehicle the SQL starts to look like this:

SELECT v.id, v.model_id, m.make_id, k.type_id
FROM vehicle v
LEFT JOIN model m ON v.model_id = m.id
JOIN make k ON m.make_id = k.id
JOIN type t ON k.type_id = t.id

And so on.

Answer 3

What you are looking for is a CHECK constraint. Unfortunately MySQL does not currently support this. You could emulate such functionality with triggers but you would need to create both an INSERT and an UPDATE trigger for it to work.

However, as other answers have indicated, all you should really be storing is the vehicle model. In you application you should be drilling down to the type if it's available.

Answer 4

Here is one approach:

- One make (Ford, GM, Honda) can have many models, one model belongs to only one make.
- Model is of a certain type (car, truck bike).
- Vehicle is of a certain model. One vehicle can be of only one model; there can be many vehicles of a model.

Model table contains columns common to all models; while car, truck, and motorcycle have columns specific to each one.

When modeling a DB, consider data, entities and relationships; don't start from the UI -- there is a business layer in between to sort things out. It is OK to use MySQL, you can enforce check and foreign key constraints on your application layer.

Answer 5

I see you already accepted an answer, but an alternate approach that handles your actual structural problem and doesn't use triggers or check constraints would be to create dummy entries in the Make and Model tables with a description of "n/a" or such, one for each entry in Type and Make respectively, and then get rid of the redundant columns in Vehicle.

That way, if all you know is the Type of a vehicle, you'd find the dummy entry in Make that references the appropriate Type, then find the dummy entry in Model that references that Make, then reference that Model from the new row in Vehicle.

The main downsides of course would be extra housekeeping to create the dummy rows, either ahead of time when adding a Type or Make, or on demand when adding a Vehicle with missing data.