Car service database design (domain model)

The General Advice:

When you are starting off learning how to model databases, one of the most important rules of thumb is: Every tangible thing that matters to your system is probably an entity type.

This is a really good place to start with any logical database design. If you spend some time up front thinking about what kind of things matter to your system, then you're going to come up with a solid foundation on which to build your system. The things your organization cares about will change much less frequently than the business processes and rules your organization uses to deal with those things. That is why a solid data model is so important.

Another important rule of thumb is: Normalize your data model by default and only denormalize when you have a (really) good reason to. This is especially true for a transactional system. Reporting systems and data warehouses are a different story.

The Specific Answers:

Cardinality: If you think about it, it is easily the case that a car could have never been serviced (by your shop). Therefore a minimum cardinality of zero is very plausible. On the other hand, by the time the vehicle matters to your system it may well be because it has had its first service - so a minimum cardinality of one is also plausible. You need to think about what the business rule is for your organization and model accordingly. I would think, for example, that a car dealership would have lots of cars in its system that haven't been serviced by the dealership yet, whereas a muffler shop wouldn't care about cars it hasn't serviced.

Service Items: You asked:

Also, a service involves parts, labor, and consumable.

How would you model this? As a separate entity? Or in the service entity or part of the relation (intersection entity) between car and service ?

Let's consider an intersection entity between car and service... You could potentially use such an intersection to store details about the service, like how much labour, which parts, and consumables were used.

However, using an intersection implies a many-to-many between cars and services, but you've already stated that each service is for (exactly?) one car. Using an intersection entity to track service item details would mean your model isn't properly normalized.

Consider this model as an alternative:

In this model each service is for one vehicle, but each service can have many instances of labour, parts and consumables. This model follows the first rule of thumb I mentioned and makes an entity type out of each tangible thing the system cares about. This might be a good first stab at a logical model.

One of the issues with the above model is that it doesn't handle one aspect of how your system is likely to want to use the data, at least not very well. One of the most important reasons for tracking all of this data in your system at all is so that you can print off an itemized service invoice. That means that a service line item is itself a thing which is important to your system. If you take that into consideration, you might end up with something more like this:

Notice in this second alternative SERVICE_LINE_ITEM is recognized as an entity type. It is an intersection between SERVICE and the generic line item type: SKU. A SKU is a supertype entity that could be a part, a consumable or some kind of labour. You don't need to have a logical supertype for service line item types, but a lot of systems would be modeled this way because it makes the transactional detail much simpler.

This second model introduces abstract entities over and above the concrete entities of the first model. Introduction of abstractions like this is one of the things that tends to happen as you move from an initial logical model, based mostly on tangible things to a physical model.

As you gain experience with data modeling, you'll get good instincts for moving past the conceptual/logical model stage directly to a well structured physical model.

Perhaps something like this:

CREATE TABLE make (
    make_id INT NOT NULL,
    make_name VARCHAR(25),
    PRIMARY KEY(make_id);
);

CREATE TABLE model (
    model_id NOT NULL,
    make_id NOT NULL,
    model_name VARCHAR(25),
    PRIMARY KEY(model_id)
);

CREATE TABLE model_year (
    model_id INT NOT NULL,
    year_num INT NOT NULL,
    PRIMARY KEY (model_id, year_num)
);

CREATE TABLE transmission (
    transmission_id INT NOT NULL,
    transmission_descrip VARCHAR(15),
    PRIMARY KEY (transmission_id)
);

CREATE TABLE drive (
    drive_id INT NOT NULL,
    drive_descrip VARCHAR(10),
    PRIMARY KEY (drive_id)
);

INSERT INTO transmission (transmission_id, transmission_descrip)
VALUES (1, 'Auto'), (2, '4-speed manual'), (3, '5-speed manual');

INSERT INTO drive (drive_id, drive_descrip)
VALUES (1, '2WD Front'), (2, '2WD Rear'), (3, '4WD'), (4, 'AWD');

To allow someone to add a 2005 Toyota Camry, first you need to make sure Toyota is in the make table, Camry is in the model table, and 2005 is available for the Camry:

INSERT INTO make (make_id, make_name)
VALUES (1, 'Toyota');

INSERT INTO model (model_id, model_name)
VALUES (1, 1, 'Camry');

INSERT INTO model_year (model_id, year_num)
VALUES (1, 2005);

We keep certain data on car buyers at my job, and in selecting the car type (either currently owned or looking to buy), we have them select the year, then make, then model and options from drop-downs. Each drop-down is populated on the fly based on previous answers. First the year:

SELECT DISTINCT year_num FROM model_year;

Then the make (you can't pick Pontiac for 2013):

SELECT DISTINCT m.make_id
FROM make m
INNER JOIN model mo ON mo.make_id = m.make_id
INNER JOIN model_year my ON my.model_id = mo.model_id
WHERE my.year_num = @some_year_variable;

Then the model drop-down gets populated based on:

SELECT DISTINCT model_id
FROM make m
INNER JOIN model mo ON mo.make_id = m.make_id
INNER JOIN model_year my ON my.model_id = mo.model_id
WHERE my.year_num = @some_year_variable
    AND ma.make_id = @some_make_variable;

You get the idea. As far as constraining other options for a model, there are a few options, based on how strict you want to be, how much time you want to spend managing it, and how much you know about the auto industry.

Since all cars have a drive type and transmission, you might want to make those separate tables, like:

CREATE TABLE model_transmission (
    model_id INT NOT NULL,
    year_num INT NOT NULL, -- include this to restrict tranny based on some years 
                           -- certain models had different trannies offered   
    transmission_id INT NOT NULL,
    PRIMARY KEY (model_id, transmission_id) -- include year_num in PK if it is in the
                                            -- table
);

CREATE TABLE model_drive (
    model_id INT NOT NULL,
    year_num INT NOT NULL, -- again, if you are up to this level of control
    drive_id INT NOT NULL,
    PRIMARY KEY (model_id, drive_id) --include year_num in PK if it is in the table
);

Or you could use an option table and a model_option table, like this:

CREATE TABLE option (
    option_id INT NOT NULL,
    option_descrip VARCHAR(25),
    PRIMARY KEY (option_id)
);

INSERT INTO option (option_id, option_descrip)
VALUES (1, 'Transmission'), (2, 'Drive'), (3, 'Doors');

CREATE TABLE model_option (
    model_id INT NOT NULL,
    option_id INT NOT NULL,
    option_value VARCHAR(15),
    PRIMARY KEY (model_id, option_id) -- include year_num as above if desired
);

INSERT INTO model_option (model_id, option_id, option_value)
VALUES (1, 1, '1'), (1, 2, '1'), (1, 3, '4'); -- All Camrys now have automatic,
                                              -- front-wheel drive, and 4-door options
                                              -- available

I prefer the first approach as you can see the problem with referential integrity inherent in the second, plus it makes it more awkward to restrict available options. The table for listed cars might be like this:

CREATE TABLE car (
    listing_id INT NOT NULL,
    year_num INT NOT NULL,
    model_id INT NOT NULL,
    transmission_id INT NOT NULL,
    drive_id INT NOT NULL,
    doors INT NOT NULL, -- might want varchar to include 'hatchback' or other options
    mileage INT,
    customer_id INT NOT NULL,
    PRIMARY KEY (listing_id)
);

If this doesn't address a particular feature you believe you need just add a comment.