locked
vehicle order and inventory table design RRS feed

  • Question

  • Hi can someone help me with my table design to track orders and inventory management?  This will eventually go into a SQL database.   I attached a link to the design.

    Specifically, I'm having trouble understanding the many to many tables in my design and I'm really not sure if I should have

    so many-to-many tables or if the entire design is incorrect.

    Here's the process:

    A department places an order for a vehicle with the purchasing dept. the purchasing dept creates an order for a vehicle.  Because an order can have many vehicles, I added the VEHICLE ORDERS table (it is a many to many relationship).  So this table takes care of any vehicle orders.

    However, a vehicle order also requires parts in order to build it so I created a PARTS table and since I also may need to order parts for my vehicle I added a VEHICLE PARTSORDERS as a M:N that links PARTS and VEHICLES together. 

    So I can complete my vehicle order if all of the parts for the vehicle are in stock.  If they are not in stock I will need to order the part from the supplier. I believe My VEHICLE_PARTSORDERS takes care of this but I'm not sure if I need another table for just the part orders instead of using the M:N table (VEHICLE_PARTSORDERS) to do this for me.

    Can anyone please help?  I am really feeling stuck on this one.

    Any feedback is appreciated.

    Thank you.


    • Edited by grga270 Monday, April 8, 2013 6:23 PM
    Monday, April 8, 2013 1:41 AM

Answers

  • If you have difficulty writing the requirements, you will only magnify the problem trying to translate that into a database design.  You first wrote "A department places an order for a vehicle".  Later on, you say "an order can have many vehicles".  To confuse things, you also said that the purchasing department creates an order a vehicle.  Quite simply, you intermix singular and plural terms as well as use duplicative ownership / creation responsibility.  Take a step back and forget about who does what.  Focus on "what" first.  Identify your entities and their attributes.  

    In addition, I'll warn you that asking someone that is not intimately aware of your particular needs / process / system to validate your design is a dangerous thing to do.  A forum like this does not really provide sufficient context or discussion to adequately understand a particular model or how closely that model matches the system it is intended to implement. 

    That said, you description of Parts and its relationships concerns me.  That term implies a "building" process, not a purchasing process.  Perhaps you meant something more like "options" or "accessories".  One thing to keep in mind is that this type of thing can change over time.  What was purchased last year (or even last month) may no longer be available - having been replaced, superceded, or simply dropped.  One should not be able to order things which cannot be purchased (or no longer exist).

    Lastly, an order management system is a very complicated system.  Your link posting did not work - but I can assure you that there will be many complicated relationships and many tables.  Your reference to inventory management is also concerning - I'm hoping you overstated your requirement here since inventory management is perhaps more complicated than order management.  I suggest you take a look at the adventureworks sample database and study the implementation - it does something similar. 

    • Proposed as answer by Fanny Liu Friday, April 12, 2013 7:51 AM
    • Marked as answer by Fanny Liu Monday, April 15, 2013 1:21 AM
    Monday, April 8, 2013 1:57 PM

All replies

  • I think you need the table Parts as you have to know what part is in stock. So vehicle has many parts that some are in stock and some aren't.

    Having vehicle_parts table tells us all parts for the specific vehicle and what parts are in stock. (InStock column ) . If you make a order and some of the parts are not in the stock the VEHICLE ORDERS's column IsCompleted is set to FALSE. Now that I would create a PartOrders table that contains what parts to what orders I need to order (it also contains OrderID,PartID,SupplierID,OrderDate and etc.). What do you think?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, April 8, 2013 5:25 AM
  • If you have difficulty writing the requirements, you will only magnify the problem trying to translate that into a database design.  You first wrote "A department places an order for a vehicle".  Later on, you say "an order can have many vehicles".  To confuse things, you also said that the purchasing department creates an order a vehicle.  Quite simply, you intermix singular and plural terms as well as use duplicative ownership / creation responsibility.  Take a step back and forget about who does what.  Focus on "what" first.  Identify your entities and their attributes.  

    In addition, I'll warn you that asking someone that is not intimately aware of your particular needs / process / system to validate your design is a dangerous thing to do.  A forum like this does not really provide sufficient context or discussion to adequately understand a particular model or how closely that model matches the system it is intended to implement. 

    That said, you description of Parts and its relationships concerns me.  That term implies a "building" process, not a purchasing process.  Perhaps you meant something more like "options" or "accessories".  One thing to keep in mind is that this type of thing can change over time.  What was purchased last year (or even last month) may no longer be available - having been replaced, superceded, or simply dropped.  One should not be able to order things which cannot be purchased (or no longer exist).

    Lastly, an order management system is a very complicated system.  Your link posting did not work - but I can assure you that there will be many complicated relationships and many tables.  Your reference to inventory management is also concerning - I'm hoping you overstated your requirement here since inventory management is perhaps more complicated than order management.  I suggest you take a look at the adventureworks sample database and study the implementation - it does something similar. 

    • Proposed as answer by Fanny Liu Friday, April 12, 2013 7:51 AM
    • Marked as answer by Fanny Liu Monday, April 15, 2013 1:21 AM
    Monday, April 8, 2013 1:57 PM