Designing a database for car dealerships

composite-typesdatabase-design

I am starting in designing a database for car dealerships (plural). I graduated recently and this is my first gig.

The idea is that I am designing a backend that couple of dealerships will use. So we have different dealerships which might have couple of branches, they have a list of car they are selling.

What I am struggling with are the cars and its parts because those are the information that will be pulled out of the database and published on different websites. This is how i started thinking about the Engine part:

enter image description here

Here I am pulling into different tables specs that might get duplicated like number of cylinders,
horsepower etc. There might be that two cars that are the same model and year have different engines for example.

Or should I do something more simple like this:
enter image description here

The idea is that for example two Toyota Corolla who are exactly the same type
and year have different specs for the engines.

Another idea that i got from looking through google how other people have done similar systems:
enter image description here

with the carParts table we can have different parts for the Same car, it works well when you want to say for example that this Toyota Corolla has Air Conditioning, if we add a number variable in the carParts you can also use it for example saying that the Same Toyota Corolla car has a different horsepower amount than another car.

I am struggling with visualizing this and I am unsure about the timing system, because if a certain car has a belt instead of a chain it has additional information that the timing chain does not.

Am I going in the right direction? If there are any Database-Yodas out there any help in the right direction would be greatly appreciated 🙂

================================
Update

So I have changed the design, it is much simpler.

https://www.youtube.com/watch?v=ZTPAMJ9MzdY

I found this video about complex relationships which answered two questions I had:

  1. Things that have is A relationship, parent child relationship and I do think i could use that concept for something like the timing equipment in cars that can either be chain or belt
  2. Things that are composed of multiple parts like a computer that can have processor, motherboard and so on. I could use it for the car concept and split Car into Engine, drive circuit instead of going further and splitting everything inside Engine in its own tables.

Normalization and redundency is more complex topic than I first thought. When thinking about cars many things are the same but the same car, manufactured the same year could have a new engine with different specs so how much can you really prevent redundency and when are really two cars a duplicate of each other.
I was also thinking at first that the a car sales company can sell many instances of the same car but that is not the issue, they each have their own ID, the exact same car can be sold by two different car sales companies, that you have to take into account.

I am sorry for my ramblings, writing what I know makes me understand the topic more. If anyone how more input please feel free to comment.
Maybe I put my updated schema here later, It is all in icelandic at the moment 🙂

Best Answer

From your first example to your second example you went between two opposite extremes of Normalization, and likely you want something in between.

For example, you'll obviously need a Cars table and there will be different kinds of things relevant to a Car, such as an Engine, Make, Model, & Year, maybe Paint (for type, color, style) etc. Some of these things make sense to normalize out such as the Engine, because as you see there are a number of Engine specific attributes. So you would want to have an Engines table. But you shouldn't over normalize things like the Stroke and Cylinders into their own tables because those are basically only one or two attributes that apply to all (or most) Engines. Things like Make, Model, & Year are probably ok to leave inside your Cars table, since they generally apply to all cars.

If you wanted to store information about the Car Paint then you probably would want to normalize the Paint attributes into their own table because there's multiple attributes for Paint. But having only one Paint attributes table is probably sufficient (as opposed to making a separate table for each Paint attribute).

So the point being normalize your tables when there are multiple related attributes that apply to most of the same object type. Don't overly normalize by breaking every attribute into its own table. Also it's ok if one of your related attributes doesn't always apply to any instance of the object type, as long as it's generally applicable. E.g. your questions regarding a Timing Chain vs Timing Belt. Unless there's a ton of attributes that apply to one and not the other, it's probably ok to store both in the same table (which likely would be the Engine table).

In regards to your last example, it depends on what the use cases and goals are of your system. In my previous example that details a more granular system that might be useful for a car manufacturer or the repair shop of a manufacturer. A CarParts table makes sense for storing all the different kinds of parts there are, with generically applicable information such as Description. It may be moreso applicable to a parts store that sells replacement parts (though could also be used in a car manufacturing system).