CODEWITHSUNDEEP
database
Chaosxmk
Chaosxmk

Reputation: 745

Should redundant information be added to a DB to ensure that any product changes don't affect the output of purchase orders

I'm trying to figure out how best to layout my database for storing records of purchases, and later on having the possibility or displaying purchases accurately. Thus far I have this:

Products

+-----------+-------------+---------+------------+
|  item_id  |  item_name  |  price  |  discount  |
+-----------+-------------+---------+------------+
|  1        |  Product 1  |  5.00   |  3.50      |
|  2        |  Product 2  |  14.99  |  12.00     |
|  3        |  Product 3  |  29.99  |  0         |
+-----------+-------------+---------+------------+

Orders:

+------------+-----------+--------------+
|  order_id  |  user_id  |  order_date  |
+------------+-----------+--------------+
|  1         |  1        |  2015-08-02  |
|  2         |  2        |  2015-08-30  |
|  3         |  3        |  2015-09-01  |
|  4         |  2        |  2015-09-03  |
+------------+-----------+--------------+

+---------+------------+-----------+-------+
|  oi_id  |  order_id  |  item_id  |  qte  |
+---------+------------+-----------+-------+
|  1      |  1         |  1        |  5    |
|  2      |  1         |  2        |  2    |
|  3      |  2         |  1        |  1    |
|  4      |  2         |  2        |  6    |
|  5      |  3         |  2        |  1    |
|  6      |  4         |  3        |  3    |
+---------+------------+-----------+-------+

My main concern thus far are;

  1. What if a product detail is change (ie. item_name, price, discount, etc)
  2. What is a product is deleted entirely

If either of those use-cases occurs, any information I try to re-display/print out will no longer have the correct information. The easy solution that comes to mind would be to have redundant data, where the order item table includes a copy of all of the columns that are important enough to keep (name, price, discount, etc). However, I have always been told to avoid redundancies as much as possible.

Upvotes: 1

Views: 39

Answers (1)

Sagar Shirke
Sagar Shirke

Reputation: 686

I would suggest for this instance it can be better to have redundant data in orderdetails table, so that in will not cause any false data display.

Ideally you should not delete any product physically instead of that you can have another column may as "IsDeleted" and use this to identify whether it is deleted item or not.

If in case you want to avoid redundancy then you need implement some logic like. You can have separate ProductPrice table where there will price, starttime,endtime columns and its primary key will foregin key in OrderDetails table.

Upvotes: 1

Related Questions