I'm developing online shopping cart software system, I've products and item_options tables.

The Items might have 1 or even more options, see below.

products table

  • item_id (PK)
  • item_title
  • item_description

Note: item costs are within the item_options tables

item_options table

  • option_id (PK)
  • item_id (FK)
  • option_title
  • option_cost

Once the customer placed a purchase, the information is going to be added in to the order and order_products table.

orders table

  • orders_id (PK)
  • customer_id (FK)
  • address_address_id (FK)
  • date_bought
  • orders_status
  • orders_date_finished

order_products table

  • orders_products_id (PK)
  • orders_id (FK)
  • item_id (FK)
  • item_option_id (FK)

There's an issue, when the employees alter the cost, item title or remove a item.. the client bills is going to be affected since the FK in the order_products will no more be been around within the products table. What's the means to fix this?

What about this solution: add active area within the items and item_options tables. Once the employees wish to alter the cost or title of the item - just turn the present active record to (items.active) after which place a brand new record using the new information and active become 1. That old orders still point the right id from the entry that's not active. Is great way doing the work?

Once the new information continues to be placed within the products table, does which means that I must update/alter the new PK ID within the item_id.item_options table?

In something similar to this, I'm certainly against using hard removes. Every update or remove ought to be adding a brand new row in to the item_options table after which marking the prior one as inactive (this way there's absolutely no way that the user can choose among the previous values).

Each new item placed should contain two steps:

  1. Add the product towards the products table
  2. While using item_id in the products table, add the product in to the item_options table

The normal means to fix this that I have seen would be to keep snapshot from the data that you would like past inside a separate table. That method for you to store just the data you'll need a snapshot of during the time of the invoice, rather than all data inside a particular table.

It will help to keep history data from live data if this involves simplifying confirming and queries within the code.. generally both of these things don't have to be utilized simultaneously, which means you steer clear of the more complicated queries (and heavier load on one table) simply to access either.

If you are planning to complete things how you describe, you will need to give a new rows towards the item_options table to handle new products.