Data that References Itself (1 Viewer)

[RogerCooper]

I have a business requirement that poses an interesting design question. My company makes dyes that detect leaks in fluid systems. All chemicals require an SDS (Safety Data Sheet) that specifies safety information about that chemical. The SDS is produced by specialized software, so I need to do is keep track of the link to where it is stored on our drive. The typical use of this information is to print out all the SDS's required for a specific order.

The complication is that sometimes we sell products which are kits containing multiple dyes, each with its own SDS. So the question is what is the best way to keep track of this.

Approach #1 Create a double-keyed table called PRODUCT_COMPONENT which relates the product numbers with the individual components for the kits only. To print the SDS I would need to combine 2 queries, one for the kits and another for non-kit products

Approach #2 Create a double-keyed table called PRODUCT_COMPONENT for all products, kits or not. If the product is not a kit, the value of COMPONENT will be the same the value of PRODUCT. To print the SDS's I would have query that links PRODUCT_COMPONENT with the table that stores information fields about our products,

Approach #1 has less data entry.

Approach #2 avoids need for left joins and iif's. It also easier to validate as I can easily test that all saleable products have at least 1 entry in PRODUCT_COMPONENT.

Which approach is better. I am leaning towards approach #2 as in most cases I can identify automatically non-kits by their description and populate PRODUCT_COMPONENT.

 

[Pat Hartman]

Approach #2 is the better solution if I understand it correctly. If in some cases you have a many-many relationship, then you ALWAYS have a m-m relationship and you need a junction table. This approach is NOT more data entry although it does change the design of your data entry form. You will need to use a subform rather than a single textbox to enter the data. The subform has ONE entry if there is only one dye otherwise it has multiple entries. You can make it relatively small assuming that there are probably not more than three dyes at a time. The subform can expand as necessary but you want to optimize the space on the form so that it handles the majority of cases. So figure out what the Mode is and make the subform long enough to accommodate that number of dyes to be visible at one time.