Denormalize Design Modification

application-designdatabase-designdenormalization

Our DBA is on medical leave, I am just a developer trying to do my part. Initially, the bean counters were going to use quickbooks. However, after seeing how much time they could save with custom development they have creeped my scope.

Rather than having a quantity of a particular type of shoe in a given size (ShoeInventory.Quantity) each individual pair of shoes needs to be accounted for and thus be given an Id, LeatherCost, ManufacturingCost, InventoryCost and TotalCost.

My question: Is there any foreseeable problem if I just move LeatherCost, ManufacturingCost, InventoryCost and TotalCost from the Shoe table to the ShoeInventory table?

When an order of shoes arrives from a ShoeOrder I will then just add a record to ShoeInventory for each pair ordered rather than updating a quantity?

I have no reason to think that this is a bad idea but it seems to easy to actually be the right answer.
enter image description here

Best Answer

If you move these additional costs to ShoeInventory then they will be associated with your ShoeInventory entity. This entity also has a relationship with ShoeSize. If I understand your schema (and I might not, so correct me if I'm wrong), the implication of this is that different sizes for the same shoe type could potentially have different sub-costs. This might give you extra flexibility, or it might make things more complicated. If you want to separate this data from shoes and don't want it attached to any other table (in case it doesn't make sense), you could create a shoeCost table:

shoeCost
---------
  id (PK)
  shoe_id (FK to shoe.shoeId)
  leather_cost
  manufacturing_cost
  inventory_cost
  total_cost (this could be a computed column of
              leather_cost + manufacturing_cost + inventory_cost,
              if it makes sense to do that)
Related Question