locked
How to track the Sales order from creation to execution? Best Design. RRS feed

  • Question

  • Dear All,

    I am developing an sql db for tracking the sales order. I want to know the best table design to track the sales order.

    In a scenario, The sales order will travel in various units, Each unit will have various sales order status with substatus.

    I have to track from creation of sales order to end of the sales order.

    i want each and every movement of SO has to be tracked.

    any tips.

    Saturday, November 19, 2011 10:39 AM

Answers

  • You can have a Sales_orders table and another table, say, Units_Tracking, that will have each unit current location and status recorded. This table with also have foreign key to the main Sales_Orders table. This way you should be able to track each order unit current location and status.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Peja TaoEditor Tuesday, November 22, 2011 3:10 AM
    • Marked as answer by Peja TaoEditor Friday, November 25, 2011 1:45 AM
    Sunday, November 20, 2011 5:25 AM
    Answerer

All replies

  • If I'm misinterpreteting your description, please forgive me.

    Each sales order can have only *one* location, status and substatus at any given point in time, correct?

    In that case, I would put columns for location, status and substatus in the sales order table. The challenge for your application(s) will be to ensure that those columns are kept up to date as each sales order moves through your system.

    Saturday, November 19, 2011 6:21 PM
  • You can have a Sales_orders table and another table, say, Units_Tracking, that will have each unit current location and status recorded. This table with also have foreign key to the main Sales_Orders table. This way you should be able to track each order unit current location and status.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Proposed as answer by Peja TaoEditor Tuesday, November 22, 2011 3:10 AM
    • Marked as answer by Peja TaoEditor Friday, November 25, 2011 1:45 AM
    Sunday, November 20, 2011 5:25 AM
    Answerer
  • Have you identified the main entities the db will be tracking?
    Monday, November 21, 2011 1:43 PM
    Answerer