Database Design for Liquid Inventory Management System

[Lightwave]

Dear All - I thought I had posted this already but can't seem to find it. That's what happens when you are a bit rusty. Hope everyone is well.

Looking to see if anyone can point me in the direction of a good skeletal design of a inventory management system for liquid aclohol management system. So distilling alcohol and then splitting it up into bottles of various sizes. Probably make something up myself but would like to canvas opinion as well.

Much appreciated.

 

[AccessBlaster]

This site has a few diagrams of inventory, might be worth looking at.

http://www.databaseanswers.org/data_models/index.htm

 

[Pat Hartman]

I once did an application for a wholesale florist. They bought boxes and sold dozens or stems so there was always translation going on. Especially since a "box" might hold a different quantity of stems depending on what type of "flower" we were talking about. The only pure liquid inventory I ever worked with was for a company that painted markings on highways and parking lots. They kept gallons, drums, tanks, trucks, etc. The thing they didn't tell me was that when they were measuring the amount of paint in a truck at the end of the day, they were measuring the "air" rather than the actual paint so all my calculations were backwards. And finally, I did one for Clairol which provided formulas for making shampoo and hair dye. Most formulas were by weight but some were by volume. Most ingredients were liquid but not all.

The key to your process will be a conversion table. To standardize conversions, you might pick a UOM and use that as the pivot. Everything converts to that UOM or from that UOM so conversions might be two stages. A to B and B to C rather than A to C.

 

[Lightwave]

Thank you guys much appreciated

 

[Pat Hartman]

You're welcome. When you have specific questions, we'll help if we can.

 

[jdraw]

Do you have some "business facts" or overview of the things and processes involved? There must be a requirement/statement of need or something. I agree that some standardization for UOM will be key.

 

[Thales750]

I build Chemical Plant databases.
At the structural level, any tank that can hold liquid is in the same table. This includes trucks in, trucks out, and every tack and reactor (a place where the material is modified). Then transfers From and To. Transfers that modify materials will need to show that on the "To" side of the transfer.

This is theoretically a continuous process, so you break it down into batches.