I've been doing a lot of research and have pieced together various things from stackoverflow and other forums, but have struggled to get a comprehensive answer.
The requirements for the warehouse management are as follows:
- Be able to track item inventory with lot numbers which have expiration dates (total quantities, locations)
- Be able to determine how many pallets total were stored over the coarse of the month (prorated to the day) Billing is based on how many pallets are stored per day, with a day being a minimum duration (even if stored for an hour). Billing will be done at month end.
- Be able to track when items are received or shipped
- Get total current inventory at item level or product level
- Get total current inventory based on lot number or expiration date
- Take transfer orders (sales order is common term too) ahead of time and pull inventory based on the order
- Take storage orders ahead of time and add inventory based on unloading (should this be the same entity as transfer order?)
I think I'm close, but know I'm missing some key pieces. I struggle to understand when and how to create transactional tables that are normalized properly. I've gathered that "license plating", or giving each pallet it's own record and ID, may be beneficial for ease of inventory management operations.
The following is the database design I have thus far:
Note that I'm not worried about specific data types yet, just the general design and associations to meet the business requirements. Also note that this model does NOT need to consider costs or prices of each item.
Question: What should I adjust about this schema to properly capture business requirements?