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:
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.
The idea is that for example two Toyota Corolla who are exactly the same type
and year have different specs for the engines.
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 🙂
So I have changed the design, it is much simpler.
I found this video about complex relationships which answered two questions I had:
- 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
- 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 🙂