Help with table structure for parts parts (1 Viewer)

oxicottin · Jul 31, 2019

Hello, I a database that I'm creating and need help with its structure that pertains to my products parts and I cant figure out. I have a Product and each product has components to make that product and each component has different specks, for instance...

Product1 uses component1 but there are many different sizes for the component1 that is used for that particular Product1. how would I set up a table to hold different sizes for each component?

I attached a .pdf what I have so far in a .zip because it said I was over 100kb?

plog · Jul 31, 2019

This depends on how "size" is defined for each component. Does every component share the same sizes (Small, Medium, Large, etc.)? Or does each component have its own sizes (Screw=1/4, 1/2, 1/8; Dry Wall=4x4, 8x8, 2x2)?

If the former, you simply add a size field to your tbl_Components. If the latter you will need a new table (tbl_ComponentSizes) to sort out all the component/size permutations and then you use tbl_Components as a junction table between tbl_Product and tbl_ComponentSizes.

Also, whatever you do, you should not have a table that numerates field names (Component1, Component2, Component3...etc). That is improper. You don't accomodate data horizontally (with more columns) but vertically (with more rows). Again, the answer to how generic sizes are will determine the new tbl_Components strcutre.

oxicottin · Jul 31, 2019

The component name was for ease of showing in the forum I will name them properly. Each component has its own sizes so would I create the table "tbl_ComponentSizes" but field names in the table would be the same as fields in the junction table. example below..

jdraw · Aug 1, 2019

I suggest you review this thread and all its posts for an interesting dialog with a similar situation --Parts , components and assemblies.
Good luck.

plog · Aug 1, 2019

Each component has its own sizes

Using that I created my own image of what your tables and relationship should look like. For clarity, I got rid of tbl_Components, a better name for the table that lists which components go to which products would be tbl_ProductComponents, I've used that one in my image.

oxicottin · Aug 1, 2019

plog said:
Using that I created my own image of what your tables and relationship should look like. For clarity, I got rid of tbl_Components, a better name for the table that lists which components go to which products would be tbl_ProductComponents, I've used that one in my image.

Thank You... I got it now, it explains itself better when you put a little data in.

oxicottin · Aug 2, 2019

Plog, Looking at your image the component name and component size. I have 11 components and each component has anywhere from 2 to 10 sizes. In the tbl_ComponentSizes would I enter data like the image?

arnelgp · Aug 2, 2019

in real world, not entirely correct.
you see each items in your product has its own unique specs.
therefore each item/component must be a single record.
item/component can be identified by the Identification Number (Ident) or Manufacturing Number or Serial Number or Part Number or Heat Number, etc.
so:

Code:

Ident               IdentName
----------------------------------
B001               Machine bolt 1" x 4"L
B002               Machine bolt 1" X 4.5"L
S001               Stud bolt B7/2H 3/4" x 2.5"L
S002               Stud bolt B7/2H Flouropolymer Coated 3/4" X 2.5"L

if you have Assembly, then your screen capture will apply:

AssyNo            AssyName
------------------------------------------
M001              Motor (capacity @)


the junction table:

AssyNo          Ident
----------------------------------------
M001            B001
M001            B002

oxicottin · Aug 2, 2019

arnelgp, I see what your saying but for the data retrieval perspective im not getting it. in my case using plog's table image.

Code:

[B]tbl_Product[/B]
(pk)ProductID   Product    IsInactive
-----------------------------------------
     1          B1OS           No
     2          B2OS           Yes
     3          B2             No

[B]tbl_ProductComponents[/B] [COLOR="Magenta"]Junction Table[/COLOR]
(pk)ProductComponentsID   ID_Product    ID_ComponentSize
-----------------------------------------------------------
            1                 1                 2
            2                 1                 7
            3                 1                 8
            4                 3                 6

[B]tbl_ComponentSizes[/B]
(pk)ComponentSizeID   ComponentName    ComponentSize
------------------------------------------------------------
           1            Feed Wheel          125
           2            Feed Wheel          90
           3            Feed Wheel          84
           4            Feed Wheel          146
           5            Cut-Off             84
           6            Cut-Off             90
           7            Cut-Off             125
           8            P Plate             2-1/2
           9            P Plate             3
           10           P Plate             2-1/16
           11           P Plate             3-1/2

So my results would be
1) B1OS would have a Feed Wheel thats size is 90
2) B1OS would have a Cut-Off thats size is 125
3) B1OS would have a P Plate thats size is 2-1/2
4) B2 would have a Cut-Off thats size is 90

I get all the above, what im not getting is how I can retrieve the data from a query or even show on a form to enter the data or new data. Im used to creating a table for lets say a component name not have several in one table.

something like below

Code:

[B]tbl_FeedWheel[/B]
(pk)FeedWheelID   ComponentName    ComponentSize
------------------------------------------------------------
           1            Feed Wheel          125
           2            Feed Wheel          90
           3            Feed Wheel          84
           4            Feed Wheel          146

[B]tbl_CutOff[/B]
(pk)CutOffID   ComponentName    ComponentSize
------------------------------------------------------------
           1            Cut-Off             84
           2            Cut-Off             90
           3            Cut-Off             125

Help with table structure for parts parts (1 Viewer)

oxicottin

Learning by pecking away....

Attachments

plog

Banishment Pending

oxicottin

Learning by pecking away....

Attachments

jdraw

Super Moderator

plog

Banishment Pending

Attachments

oxicottin

Learning by pecking away....

oxicottin

Learning by pecking away....

Attachments

arnelgp

..forever waiting... waiting for jellybean!

oxicottin

Learning by pecking away....

Similar threads

Users who are viewing this thread