2

So I have been trying to design a inventory management system for a Car Showroom. The problem with this design is that I can get a purchase but on selling the product I can't link it to my purchase. The idea I've came with up is add a Batch_ID to my Inventory_Details table but I can only link that Batch_ID in my details table not in my inventory table. Any suggestions will be helpful.

Inventory System Design

5
  • I removed the conflicting DBMS tags. If you want a solution that exploits specific DBMS features, then please only add one tag for that DBMS
    – user1822
    Commented Mar 27, 2021 at 8:02
  • why sale and purchase ids are related to the same field of inventory? Commented Mar 27, 2021 at 8:18
  • Because I can easily join my sale or purchase with inventory table to find all my purchases or sales Commented Mar 27, 2021 at 8:20
  • Hi, and welcome to the forum! I would have "stock_sold" and "stock_to_hand" (i.e. inventory) in different tables. When a purchase is made, the car goes into the inventory table and when one is sold it goes into the stock_sold table... you could have a BOOLEAN (exact name will depend on RDBMS) is_sold field in inventory and just have one inventory table to simplify?
    – Vérace
    Commented Mar 27, 2021 at 12:23
  • The problem with a flag is_sold will be I need to track my sale and purchase independently of each other because of my inventory_details. e.g If a car is bought from the Vendor we say our purchase inventory_details file column is set to received but when the item is sold inventory_details file column is set to not received. With this the details should show on purchase the file was received and otherwise sold the customer didn't received it. @Vérace Commented Mar 28, 2021 at 11:15

2 Answers 2

1

In a traditional Inventory Warehouse System or Warehouse Management System (WMS) whenever a new Item is put on hand (i.e. created or purchased, and therefore added to Stock) a unique identifier must be created, usually a serial number, before you can enter it into Inventory and adjust your quantity on hand. In the context of a Car Showroom, I would imagine this would the be the VIN instead. (I'm not an expert on cars but I believe that's a unique identifier.)

Even when an item is received in batch quantities, that many unique identifiers need to be added to your Inventory before you can add the quantity to your quantity on hand. Therefore if your Purchase table has a one-to-many relationship with how many cars a Purchase can buy, because of batches, then you can't directly relate it to your Inventory table (yet). Rather you should have a separate table such as PurchaseLines which is many-to-one with your Purchase table, and has a row with the unique VIN for each car that was purchased. (This is even a simplification, as a real WMS actually normalizes this further into at least 2 more tables for ledger purposes, but I think that's outside the scope here, and this should be sufficient to what you need.) Your InventoryDetails table would then contain the VIN as well and directly relate back to the PurchaseLine it came from.

Additionally you should have just an Items table that defines the criteria of your cars, so you can generically refer to your quantity on hand by Item. As I'm sure you don't want to track the individual quantity of a VIN which will always be 1 (since my changes above will result in that). Instead if you have an Items table with fields like Model, Description, Color, MFYear, etc then your InventoryDetails table becomes more normalized and only stores an ItemId field instead (one-to-one with the Items table). Then your Inventory table can now track quantity for an ItemId instead which would be the relationship between InventoryDetails.

Finally, the concept of an ItemId becomes universal throughout the Warehouse Management System. It is like your core unit of measure. It should be part of your Purchases (in PurchaseLines) and your Sales (in an equivalent SalesLines table as well), and is a way to establish what item was transacted on.


Additional Resources:

  1. Example ER Diagram of an Inventory Management System
1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Commented Mar 31, 2021 at 8:22
0

Would you consider relating purchases and sales car_ids ?

Why not relate car_ids together? A car_id relates a purchase with a sale uniquely.

So in case, that car is sold you can relate it directly to the corresponding purchase that was done before.

1
  • You trying to say replace ps_id with car_ids? The problem with this is if I do that, consider if purchase multiple car's and want to check the cars purchases as batch wise, I will not be able to distinguish which car is from which batch. Commented Mar 27, 2021 at 8:29

Your Answer

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

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