I have a few notes on a similar but slightly different closure table on this page.
Once I've got the data structure, does anyone have any ideas for how best to show historical data for a vial?īill Karwin covers the alternatives well in his presentation. So the data structure is the first issue here. There is already redundancy here because the batch number is on each node whereas it really only needs to be on the first node after manufacture (it clearly doesn't change when the batch is aliquoted). A node represents a vial being in a location so each line has an AutoNumber primary key, a 'parent' (the primary key of the previous node - either the previous location of the vial, or the parent vial if it was aliquoted), a 'location' field, the batch number etc. I'm struggling to find the best way to record this data currently I have one main table (plus several lookup tables) which has one line for each 'node'. That is, if I look at the history of vial #7 in the above image, I want to see the history of vial #7, (reverse chronologically, for example), followed by the histories of vials #6, #3 and #8. Where it has been (and when), including the history of its 'parent' vial and the parents of that. I would like to be able to track the history of any particular vial. These vials may be transported at any time, and may be aliquoted further. One is kept in storage, the other is shipped somewhere else and further split into 50 1g vials. That is, we may manufacture 100g of a product, which is aliquoted into two 50g vials. I'm designing a database in MS Access which is used for tracking the locations of a variety of chemical solutions, made in batches.Įach batch can be split (aliquoted) practically indefinitely.
0 kommentar(er)
