0

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:

enter image description here

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?

Improve this question
4
  • Sorry, but what is your question?
    – dwhitemv
    Oct 11, 2022 at 18:03
  • @dwhitemv just added it, just how to properly design the schema for my purposes.
    – karns
    Oct 11, 2022 at 18:14
  • Is there any part of it you are having trouble with? Your requirements are the beginnings of an Inventory Management System (IMS), which are complex pieces of software. It is difficult to give meaningful feedback on such a large topic.
    – dwhitemv
    Oct 11, 2022 at 19:25
  • I'm mostly concerned with the inventory tracking with locations and then being able to bill by how many pallets were stored per day per month, but guidance on all business requirements as outlined would be helpful. Yes, I'm beginning to realize why so many businesses decide to use an existing software solution even if it doesn't fit perfectly! Lot of work!
    – karns
    Oct 11, 2022 at 20:25

1 Answer 1

Reset to default
0

Putting my Software Engineering hat on,

Rather than asking, "Does my schema match my requirements," you should be asking, "What are the business processes that implement these requirements?" Look at how your business is operating today -- how it accomplishes the objectives set out in the requirements list -- and use those methods to understand how objects (and data about them) flows through the enterprise.

Once you understand the data flow and workflows in your business, then you can translate those flows into a representation of that data in software, knowing that they reflect real-world objects and processes.

Its a common mistake to start developing a schema without truly understanding what you are modeling. Its the "when all you have is a hammer, everything looks like a nail" problem; when all you have is a RDBMS, everything looks like a schema.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.