Currently I am developing Multi-Branch Retail software. All branches have the same set of items. However, each one has its own stock quantity.
My question is, what is the best practice to apply in branch quantity control for items?
I am currently thinking about the following design:
Item Table ======== -Id -Description Branch Table ======== -Id -Description Branch Quantities Table ======== -Id -ItemId (FK to Item Table) -BranchId (Fk to Branch Table) -Quantity (This contains the quantity for a specific item)
Is this a good design? or any suggestions?
2
Since all branches identify items the same way by codes and name, a single item table that doesn’t differentiate for branches is fine.
You have a many-to-many type relationship here with items and branches, so there are no limits there as far as combinations. It would be easy to put a unique constraint on itemid & branchid since you have a surrogate key instead of a natural key.
You’re fairly normalized here. There’s no need to update data in more than one place unless your keys change. No reason why that should happen. You can throw some cascading updates and deletes if you see fit.
The quantity field is a questionable. How does that get updated? Does the system “do the math” for purchases and sales? Is there someone in the store who does an inventory count and updates this number? You may need some function to recalculate, make adjustments if it gets out of whack. It helps with performance to have this number pre-calculated, but there are draw-backs.
If this is the only data in the system for quantity, the real problem is you can only tell me the quantify of the item since it was last updated. How many did I have at the end of last month? Have there been items added to the inventory that have never been sold?
Discuss this with the project owner or someone who knows how to do this on paper to see what it needs to do.
This is a good start, possibly suitable as a classroom assignment. If I had no more information than what is presented in the question this is what I’d use.
As MichaelT noted in the comments, a production system would likely be much more complicated. Describing such a system is beyond the scope of Programmers.
If this is a work assignment, you’ll eventually need a lot more detail from your business analysts to clearly define what you are building. “Multi-branch retail software” isn’t very descriptive – the final solution could be anything.
You might want to do some googling on Enterprise Resource Planning and/or Customer Relationship Management.