First off, hello there
I am trying to design a database for tracking Legos, by sets and parts.
I am having a hard time getting the relationships right, to make sure that the data cross references itself properly.
I have 5 Tables
tblParts
-PartID pk
-PartNum
-PartName
tblPartCategory
-CategoryID pk
-CategoryName
tblSets
-SetID pk
-SetNum
-SetName
-YearReleased
-ThemeID
tblThemes
-ThemeID
-ThemeName
tblColors
-ColorID
-ColorName
Pretty simple right? Ok here is where I get screwed up. How I think the relationships should be based on the following information.
A Lego Part can be in many different colors,
And Colors can go to many different parts
(So a 1 x 1 x 1 brick can be in yellow, blue or red- While The color red can goto a 1x1x1 Brick, a 1x2x3 Brick and a 1x12 Wing)
So this would be a many to many relationship
A Lego Part can only be in one partcategory, where a partcategory can have many different parts. One to Many
A Lego Part, along with whatever color it is, can go to a set. And a set can have many different parts in many different colors. Again Many to Many
A Set can only belong to one theme. But a theme can have many different sets. One to Many
So I am confused with how the many to many relationships should be set up.
Because I think it should work like this:
I want to see what parts are in a set. So I tell the database, here is the SetNum I want to see, and it runs a query to display all the parts in that set, along with the colors of those parts and the quantity of each part.
Or am I wrong in thinking that?