DB Design & Table Setup Help

[Zanzibarjones]

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?

[jzwp11]

It appears as though your reasoning is sound; now we just have to translate that into your table structure. I've shown some corrections and the junction tables below in red:

tblParts
-PartID pk
-PartNum
-PartName
-fkCategoryID foreign key to tblCategory

tblCategory
-CategoryID pk
-CategoryName

tblColors
-ColorID
-ColorName

tblPartColors (junction table to show many-to-many relationship of parts/colors
-pkPartColorID primary key, autonumber
-fkPartID foreign key to tblParts
-fkColorID foreign key to tblColors



tblSets
-SetID pk
-SetNum
-SetName
-YearReleased
-ThemeID

tblSetPartColors (junction table to show part/colors attributable to each set)
-pkSetPartColorID primary key, autonumber
-fkSetID foreign key to tblSets
-fkPartColorID foreign key to tblPartColors
-longPartQty (a quantity field to say how many of the particular part/color are in the set)



tblThemes
-ThemeID
-ThemeName

[Zanzibarjones]

Ok cool. That makes sense.

I can see how the info would be placed and would show up. Nice.

Onto another design basics question. Lego makes a lot of parts and a lot of sets. So much that the sets and parts have been separated into categories. The Themes themselves have multiple sub categories.

For instance: Theme Star Wars, has sub themes like Clone Wars, Episode I, Episode II, Episode III, Ultimate Collector Series, etc.

The Ultimate Collector Series has a sub theme as well called Sculpture. The number of subthemes, and sub-subthemes varies depending on the root theme.
The good thing is though, they are static for the most part. I don't think any other multiple sub themes will be added any time soon.

Are the sub categories something I would keep in separate tables themselves?

[SteveH2508]

The mantra is:

PRIMARY Key at the ONE end - FOREIGN Key at the MANY end.

The un-PC way to remember it: "There are many foreigners!"

[Zanzibarjones]

The mantra is:

PRIMARY Key at the ONE end - FOREIGN Key at the MANY end.

The un-PC way to remember it: "There are many foreigners!"

Interesting. I will keep that in my notes. Thank you.

[jzwp11]

Since themes, subthemes and subsubthemes etc are all themes they should go in 1 table

tblThemes
-pkThemeID primary key, autonumber
-txtThemeName

You will then need a junction table to relate the themes (primary) to their subthemes (secondary) or subthemes (primary) to their subsubthemes (secondary)

tblThemeRelate
-pkThemeRelateID primary key, autonumber
-fkPThemeID foreign key to tblThemes (represents the primary theme)
-fkSThemeID foreign key to tblThemes (represents the secondary theme)

[Zanzibarjones]

Ok great. Make some sense. I will come back to that portion in just a bit.
Now with all of that in mind, and in conjunction with your subtheme post

Would it be a wise decision to make Theme Tables for each Root Theme?

Meaning, when I go to enter parts for each set, containing all the parts for every set, the tblPartColorSet is going to get huge, fast.

Due to it having to keep records for each piece in each color for each set. Thousands of entries.

So referring to my above question, I can see using a table for each theme. Like tblStarwars, tblAdventeruers, and so on, to store all the parts that go with the sets in that theme. So I would have 20 tables that were moderately sized as opposed to one huge table.

Or am I missing something, where my tables will not get big due to the lookups and relationships? Won't there be one table that stores all that cumulative data?

If I am confusing you, please say so, I do that sometimes.

[jzwp11]

So referring to my above question, I can see using a table for each theme. Like tblStarwars, tblAdventeruers, and so on, to store all the parts that go with the sets in that theme. So I would have 20 tables that were moderately sized as opposed to one huge table.

No, this would be the wrong approach. It would not be normalized and trying to create searches will be a nightmare. All like data should be in the same table.

[Zanzibarjones]

Ok cool. I a, glad to have sorted that out. I understand the normalization of data. It makes complete sense. I was more worried about a table getting too big. Slowing down the retrieval of information, ya know?

[jzwp11]

I was more worried about a table getting too big. Slowing down the retrieval of information, ya know?

With properly setup indices, I don't think you will have an issue.

If you approach the 2GB size limit of an Access database, you can always split the database into front end (forms, queries, reports, modules, macros etc.) and back end (tables).

[Zanzibarjones]

Great, thank you for the information. Now I have a few more question, but they are on different subjects but in regards to databases themselves. I don't want to be accused of double posting in here, so should I keep asking in this thread, since it all pertains to db I am creating?

[jzwp11]

Feel free to continue using this thread.

[Zanzibarjones]

Ok, so I have heard it is not wise to enter data directly into a table, because of entry errors, ok, makes sense.

But what if I have like all the Lego Parts, Sets, Categories, Colors & Themes already in an excel spreadsheets.

Should I import the pertinent data into the right table, and then work with it from there using forms to complete the required data entry? (Like setting which pieces in their colors go to what set in what qty)?

[jzwp11]

First and foremost, create the normalized table structure that will hold the data.

If you have the data in other electronic formats, then yes it would be best to import it. But, I would import it into separate tables and then migrate the data to your normalized structure via append queries. After successful migration, delete the import tables. You would then work on forms for data entry/editing/searching etc.

[Zanzibarjones]

So how do I make sure that my structure is 100% (or close to it) normalized? Are there tools that can check that for me or do I need to do that manually and just study normalization?