Bill Pearson
asked on
Normalization of a table
Newbie needs help! I've been working on a database for my personal hobby shop to catalog my Inventory. During the normalization process I've run into a problem. In the INVENTORY table I have a field named "SIZE". For the majority of items a simple entry suffices. For example, a simple nut would be 1/4", 3/8", etc. However, I have 4 categories of items that a simple size description does not exist. The most complex item, END MILLS, require 7 properties to define them:
Number of Flutes, Diameter, Shank Diameter, LOC (length of cut), OAL(overall length), Material (HSS, Carbide, etc), andcoating (none, TIN, ALTIN, etc.). If I set up a table for the combinations it has tremendous redundancy. I have 50 items already and barely started:
4 3/16" (0.1875) 3/8" (0.125) 3/16" (0.1875) 2-1/16" HSS TIN
4 3/16" (0.1875) 3/8" (0.125) 1/2" (0.500) 2-1/16" HSS TIN
4 3/16" (0.1875) 3/8" (0.125) 5/8" (0.625) 2-1/16" HSS TIN
4 3/16" (0.1875) 3/8" (0.125) 3/16" (0.1875) 2-1/16" Carbide Uncoated
4 3/16" (0.1875) 3/8" (0.125) 1/2" (0.500) 2-1/16" Carbide Uncoated
4 3/16" (0.1875) 3/8" (0.125) 5/8" (0.625) 2-1/16" Carbide Uncoated
4 3/16" (0.1875) 3/8" (0.125) 3/16" (0.1875) 2-1/16" Carbide TIN
4 3/16" (0.1875) 3/8" (0.125) 1/2" (0.500) 2-1/16" Carbide TIN
4 3/16" (0.1875) 3/8" (0.125) 5/8" (0.625) 2-1/16" Carbide TIN
Question 1: If I break these down into smaller tables, how do I access them using the one field, SIZE?
Qeustion 2: If I do that, how would I create a search form that would check all the tables for a search criteion like all HSS endmills?
Sorry for the extensive nature of this. I have studied normalization for 2 weeks and am stumped here.
Number of Flutes, Diameter, Shank Diameter, LOC (length of cut), OAL(overall length), Material (HSS, Carbide, etc), andcoating (none, TIN, ALTIN, etc.). If I set up a table for the combinations it has tremendous redundancy. I have 50 items already and barely started:
4 3/16" (0.1875) 3/8" (0.125) 3/16" (0.1875) 2-1/16" HSS TIN
4 3/16" (0.1875) 3/8" (0.125) 1/2" (0.500) 2-1/16" HSS TIN
4 3/16" (0.1875) 3/8" (0.125) 5/8" (0.625) 2-1/16" HSS TIN
4 3/16" (0.1875) 3/8" (0.125) 3/16" (0.1875) 2-1/16" Carbide Uncoated
4 3/16" (0.1875) 3/8" (0.125) 1/2" (0.500) 2-1/16" Carbide Uncoated
4 3/16" (0.1875) 3/8" (0.125) 5/8" (0.625) 2-1/16" Carbide Uncoated
4 3/16" (0.1875) 3/8" (0.125) 3/16" (0.1875) 2-1/16" Carbide TIN
4 3/16" (0.1875) 3/8" (0.125) 1/2" (0.500) 2-1/16" Carbide TIN
4 3/16" (0.1875) 3/8" (0.125) 5/8" (0.625) 2-1/16" Carbide TIN
Question 1: If I break these down into smaller tables, how do I access them using the one field, SIZE?
Qeustion 2: If I do that, how would I create a search form that would check all the tables for a search criteion like all HSS endmills?
Sorry for the extensive nature of this. I have studied normalization for 2 weeks and am stumped here.
I would make a linked table (linked on ItemID) to which you could add as many of these special properties as are needed for each item that needs them. They would be selected (one by one) from a lookup table, not a table of combinations. If you can upload the database, I will see if I can add this table and a form for selecting the properties.
ASKER
Thank you for looking. The two I have been working on are ENDMILLS and Fasteners. You can see the database in not complete.
MyTools.mdb
MyTools.mdb
I don't see a table of items. I made a few tables to store the extra properties info, but the Items table (tblItems) is needed for linking. The relationships need work, but first there must be an Items table. Can you make one, or is it one of the existing tables?
ASKER
All the original items were stored in the "TOOLTABLE" but it had so much redundancy I started breaking it down.
When I started breaking things down, I made a table called "ENDMILLS" to just work with them.
Thanks for helping.
When I started breaking things down, I made a table called "ENDMILLS" to just work with them.
Thanks for helping.
If this is basically reference information, then you'd want to do as Helen suggested, which is called a EAV (Entity Attribute Value) design.
You find this most often in fixed asset systems where you need to keep track of items, most of which are different from one another. For example, a car and a desk.
Both might have a year, make, and model, but a car will have a VIN#, where a desk might have a "material type".
While these types of designs are more or less frowned upon in relational designs, they do have their place. As long as your ware of the downsides and account for them, you won't have any problems.
There are quite a few threads here on EE that discusses these:
This one has a good overall attribute design:
https://www.experts-exchange.com/questions/21963870/255-fields-is-the-max-in-everything.html
This one mixes a asset tracking database with a technical one:
https://www.experts-exchange.com/questions/23398247/Help-building-a-simple-asset-tracking-database-for-sm-business.html
You should read through these first two to get an idea of what I'm talking about.
This one here is where "projects" were being worked with, all with different attributes as another example:
https://www.experts-exchange.com/questions/27587930/Date-Fields-intable.html?anchorAnswerId=37600162#a37600162
This thread talks about some of the pros and cons with this type of design:
https://www.experts-exchange.com/questions/22474972/DYNAMIC-DATABASE-DESIGN.html
and
https://www.experts-exchange.com/questions/23714122/help-with-dynamic-forms-and-table-design.html
Which is a thread where a bunch of the Expert's here thrashed out the pro's and con's of this design type.
Take a bit to wrap your head around it, but once you do, you see how it's a great way to track the kind of info your talking about.
You find this most often in fixed asset systems where you need to keep track of items, most of which are different from one another. For example, a car and a desk.
Both might have a year, make, and model, but a car will have a VIN#, where a desk might have a "material type".
While these types of designs are more or less frowned upon in relational designs, they do have their place. As long as your ware of the downsides and account for them, you won't have any problems.
There are quite a few threads here on EE that discusses these:
This one has a good overall attribute design:
https://www.experts-exchange.com/questions/21963870/255-fields-is-the-max-in-everything.html
This one mixes a asset tracking database with a technical one:
https://www.experts-exchange.com/questions/23398247/Help-building-a-simple-asset-tracking-database-for-sm-business.html
You should read through these first two to get an idea of what I'm talking about.
This one here is where "projects" were being worked with, all with different attributes as another example:
https://www.experts-exchange.com/questions/27587930/Date-Fields-intable.html?anchorAnswerId=37600162#a37600162
This thread talks about some of the pros and cons with this type of design:
https://www.experts-exchange.com/questions/22474972/DYNAMIC-DATABASE-DESIGN.html
and
https://www.experts-exchange.com/questions/23714122/help-with-dynamic-forms-and-table-design.html
Which is a thread where a bunch of the Expert's here thrashed out the pro's and con's of this design type.
Take a bit to wrap your head around it, but once you do, you see how it's a great way to track the kind of info your talking about.
ASKER
This is very complex for a beginner. I will go through the threads and thanks.
I thought my layout would be simple but I didn't count on multiple items having multiple properties.
This is what I had started with>
tools.jpg
I thought my layout would be simple but I didn't count on multiple items having multiple properties.
This is what I had started with>
tools.jpg
The diagram doesn't allow for the most specific properties to be associated with a specific item (tool). This could be complicated, but I think it could be done by having fields for these properties in the main table, with controls for adding data to them that are only enabled for specific categories and subcategories. I will do something along these lines, and post it tomorrow.
ASKER
That sounds like what I was trying to do. With the 3 "special" categories, Endmills, Fasteners, and Reamers multiple criteria are needed to identify them. Everything else I was able to put in as SIZE only. I though maybe some type of CASE statement that opened a form based on the "CATEGORY' might be possible but I don't know.
Thank you so much for looking. I very much appreciate it.
Thank you so much for looking. I very much appreciate it.
Sorry for the extensive nature of this. I have studied normalization for 2 weeks and am stumped here.
Is the issue related to not comprehending normalization?
If it is, I may help in discussing that. Once understood, you will be able to apply your knowledge efficiently.
Otherwise, there are too much info in the uploaded database, that makes it difficult, for me, to follow.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think I see where you are going. I had actually thought something like that for FASTENERS and had two tables, tblThreadSize and tblLength where I stored the attributes for cap screws, bolts, etc. I just could not figure how to link them properly. I will work on this today. Thank you for you valuable time and advice.
I am working on it now. I am assuming that tblProducts is the main table, so I will modify it as needed.
ASKER
Thanks. This is a great group of people to work with.
My assumptions may not be correct, but this should give you something to work with. Here are some thoughts so far:
Assumptions
tblProducts is the main table
Problems
Vendor is a Text field, filled with a text number. It should be a Long Integer field, filled with a VendorID value from tblVendors. I changed it.
ThreadSize should be the same. I changed that field, and eliminated the redundant Lookup_tblThreadSize field. BTW, it is not a good idea to do lookups in tables – do them in forms instead, where they are easier to change if needed.
tblProducts_Threads is not needed. A direct link from ThreadSize in tblProducts to Size in tblThreadSize is all that is needed. Same with Length.
Assumptions
tblProducts is the main table
Problems
Vendor is a Text field, filled with a text number. It should be a Long Integer field, filled with a VendorID value from tblVendors. I changed it.
ThreadSize should be the same. I changed that field, and eliminated the redundant Lookup_tblThreadSize field. BTW, it is not a good idea to do lookups in tables – do them in forms instead, where they are easier to change if needed.
tblProducts_Threads is not needed. A direct link from ThreadSize in tblProducts to Size in tblThreadSize is all that is needed. Same with Length.
See the revised frmProducts in the attached database. There is still more work to be done, but I think this will give you a start. I ended up making a linked table for storing special property values, displayed in a datasheet subform that is only visible if the appropriate category is selected (I had to add End Mills to tblCategory). Other categories needing special properties can be handled in a similar manner. Here is a screen shot of the modified form:
HBF-Modified-MyTools.mdb
HBF-Modified-MyTools.mdb
ASKER
Thanks for the hard work. I'll download and check it when I get back home.
Many thanks.
Many thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Helen. It works using the test data. I will have to update the tables with real data a check it.
Thanks
Thanks
ASKER
Thanks all. I have both implementations working. Great learning experience.
I appreciate all the help and hard work. I rate both of you as the top resources!!
I appreciate all the help and hard work. I rate both of you as the top resources!!