I am trying to design a Lego Database, for my own use at home.
As I am sure many of you know, Lego bricks can come in many different types, and each of those types in many colors.
My question is this.
In designing this db I am trying to understand whether it is best to have a table containing just part numbers and their descriptions, and have a table for colors, and then have a relationship between the 2 to an adjoining table which contains then a unique ID for each piece in each color.
Just have 1 table that has each part listed in each color as a unique record in that table.
I am so confused as to which is the preferred method, to access the data quickly.
With only one person entering and querying the data, it is perfectly fine to have one table. if you have several people entering the data, you may end up with something like this for "How many green pieces do I have?"
where color = 'Green'
or color = 'Emerald'
or color = 'Gren'
or color = ...well, you get the point.
With only one person entering and querying the data, it is perfectly fine to have one table.
For now, I will be the only one entering data. However, at some point, I would love to pack this db out, and give it to some of my friends, so they could utilize it, however I do not want them to be able to edit the data. I want to do that on my own, solely. But that is down the road.
The I'd go with a table of parts, a table of possible colors, and a many-to-many table joining the two.
That is exactly what I would do. Just be sure when creating your associative entity, as many-to-many relationships cannot be saved, that you only put the two primary keys from the Part table (PartID) and the Color table (ColorID) in the associative entity.
Also, when naming your tables, keep them singular. It is much, much easier to manage.