Please make reference to the database design below:
Think about a scenario in which a sales order is produced in line with the prodcuts within the 'Product' table. But when the cost associated with a product changes, then your cost shouldn't be up-to-date in almost any sales orders produced previously. That cost should simply be employed for new sales orders. What should i do in order to take proper care of this kinda situation?
You have to add Cost and Cost towards the SalesOrderProduct table, and hang them throughout the first append of this table.
Your likely to desire a separate table to carry total orders. You'll simply be holding individual item orders of merely one row. Since the salesorder may be the primary key, you cannot know if you will find categories of products within the same order. Only use the main key from totalorder and reference it to a different table that may group exactly what belonged in a single order. But when this is actually the functionality you would like, then what John Easley stated works fine.