Product Catalog design, multiple ref to the same entity

[ssanfu]

Based on what you have posted, this is how I would start designing the tables and relationships. (remember, this is just a start)


So one Project can have many Drawings, but one Drawings has only one Project.
One Drawing can have many Spools, but One Spool has only One Drawing.
One Spool can have many Joints, but One Joint has only one Spool.

---------------------------------------------------------------------------------------------------------------------------------------




Adding the Look up TABLES, the relationships look like





I combined "T_Elbows" and "T_Pipes". Then I merged "T_Product_Type" into "T_Products". This may be wrong after looking at dB "3CMA_DATABASE_V0.2"


Here is my attempt at the dB

[tregnier]

Hi Gents,

Finally back with some news here!
I spent the last few days developping other aspect of the DB in order to put in place a small POC, which seems to be positiive and have some interest in my work.
I can now get back on that issue about handling parts product catalog.

SSanfu,
Thanks for your comments & trial on the DB, as you can see I ended with the same build for those parts.
I also worked on some forms to be able to edit the data, pull some list, reports, etc.

my 2 initial issue are still there and I now need to find the correct way to implement it

1 - Is the below relations correct? Basiclly I have 2 fields in "T_Joint" pointing to the same "T_WQR" --> Not sure how this should be setup?


2 - Handling the parts is still an issue, and actually a bit more complex than I thought. If I try to make a requirement list I have

• Having 1 or several tables to contain parts general specs : this is the product catalog, but each type of product doesn't have the same fileds (i.e. have an angle field, while pipe doesn't; Bolts got no common attribute with pipe or elbows; etc.)
• From this catalog of product, beeing able to create a part : This is a product + serial number to keep it simple.
• Then I need to see the parts from 2 points of view :

1. Assigning a part to a spool. this will give us our bill of material which will be used to calculate, weight, logistic aspects, etc.

• One part belong to only 1 spool but 1 spool can have many parts

2.From a Joint point of view, getting the information about which part is welded to each other part. we need to pull the information about serial number of the parts mainly here.

This last point make reconsider the relation Joint/Spool/Parts, like was suggested Mike few message previously. I guess we can consider having a link Joint-->Part-->Spool.
However I still have the issue that there will be 2 parts connected to a joint ( I assume same issue than the first one mentionned so far), and that attributes of parts will change depending on which type of parts we are considering (Pipes, Elbows, flanges ,etc)

Let me know if you have any idea or if the explanation are not clear (most likely ) to solve those 2 problems?
And thanks a lot for your help as always

NB : Really sorry but I don't know how to reduce the size of the DB... so link to get it below :
https://www.dropbox.com/s/8zn72yljx3....10.accdb?dl=0

[ssanfu]

NB : Really sorry but I don't know how to reduce the size of the DB...

I downloaded the dB from the Dropbox link provided in the previous message.

I did a "Compact & Repair", then compressed the dB (I use WinZip) - - - so here it is:


BTW: it is not a good idea to have more than 1 period in a file name.
The period (.) in the name is used to separate the file name from the extension.
I renamed the dB from "3CMA_DATABASE_V0.10.accdb" to "3CMA_DATABASE_V0_10.accdb"



(Ha! - this time I remembered to attach the zip file)