design question..

[nycdon]

I'm working on a database selling metal products. I need to add element
analysis to for quality control of purchased goods. I've created an elements
table, and a product elements table, to store the baseline values.
Products - ProductID, Product
Elements - ElementID, Element
ProductElements - ProductID, ElementID, min, max

Each product will have different sets of elements, and store these min/max
values, to be compared against quality of purchases.

When a certain product is purchased, the purchased element data needs to
also be captured, and compared to these min/max baseline values that are
stored.

My question is, for a product purchased, does make sense that rows of
elements be created to store the actual values - say if AL15 has elements of
Al, Cr, and Mg..when product AL15 purchased, can these 3 rows be
automatically created and tied to the purchase, based on knowing these 3
elements needed for the product from 'Productelements" table?

if this is efficient..any ideas how to create these rows based on
"Productelements"?

thanks!

 

[Allen Browne]

Yes: it makes good sense to have a related table where there are actual
composition records for the batch of the product you just purchased.

Presumably, when you buy a product, you record that in a table like this:
ProductPurchID primary key
ProductID what product you bought
SupplierID who supplied it (if a product can have different
suppliers)
PurchaseDate when it arrived
Quantity how much in this batch

The related table will have fields like this:
ProductPurchID which batch this row is for
ElementID what element it contains
ElementPercent what the actual percentage is.

This would be interfaced with a main form and subform. You can then flag
records where teh ElementPercentage is outside the min/max requirements.

To automatically enter the expected rows into the subform, use the
AfterInsert event procedure of the main form to execute an append query
statement for the ProductID of the record you just added, and Requery the
subform so the new records show up. If Execute is new, there's a basic
example here:
http://allenbrowne.com/ser-60.html

BTW, it might be a good idea to avoid field names MIN and MAX, as these are
reserved names:
http://allenbrowne.com/AppIssueBadWord.html#M