Tables versioning (historical tables) and Order tables relationship

If I add a new product in the products table or change the data - it will automatically add into products_history. This is done by trig开发者_运维知识库ger.

When customer placed the order, there is no need to duplicate the name of product in the order_products table.

To get the pruduct name and price from the order_products table - you will query from the products_history table not the products table

Note: I am showing simple tables for demonstration.

See the following tables and the result:

mysql> select * from products;
+----+------------+-------+---------------------+
| id | name       | price | timestamp           |
+----+------------+-------+---------------------+
|  1 | Product 63 |  2.00 | 2011-10-08 18:55:53 |
|  2 | Product 42 |  3.00 | 2011-10-08 18:55:44 |
+----+------------+-------+---------------------+

mysql> select * from products_history;
+----+------------+-------+---------------------+
| id | name       | price | timestamp           |
+----+------------+-------+---------------------+
|  1 | Product 23 |  2.00 | 2011-10-08 18:55:44 |
|  2 | Product 42 |  3.00 | 2011-10-08 18:55:44 |
|  1 | Product 63 |  2.00 | 2011-10-08 18:55:53 |
+----+------------+-------+---------------------+

The orders tables:

mysql> select * from `order`;
+----+-------------+
| id | total_price |
+----+-------------+
|  1 |     9999.00 |
+----+-------------+

mysql> select * from order_products;
+----+----------+------------+---------------------+
| id | order_id | product_id | product_timestamp   |
+----+----------+------------+---------------------+
|  1 |        1 |          2 | 2011-10-08 18:55:44 |
|  2 |        1 |          1 | 2011-10-08 18:55:53 |
+----+----------+------------+---------------------+

To get the product name and prices from the orders table:

SELECT order.total_price, products_history. * FROM  `order` 
   LEFT JOIN order_products ON order_products.order_id = order.id
   LEFT JOIN products_history ON products_history.id = order_products.product_id
             AND products_history.timestamp = order_products.product_timestamp
WHERE order.id =1 

Result:

+-------------+------+------------+-------+---------------------+
| total_price | id   | name       | price | timestamp           |
+-------------+------+------------+-------+---------------------+
|     9999.00 |    2 | Product 42 |  3.00 | 2011-10-08 18:55:44 |
|     9999.00 |    1 | Product 63 |  2.00 | 2011-10-08 18:55:53 |
+-------------+------+------------+-------+---------------------+

It seem to work fine.

Is there any major flaw in this design or what I could have done differently?

Second question about Extras... A product can have extras or without extras. Do I need to versioned for extra_group table? I have only versioned for the extra table.

See the following tables:

mysql> select * from extra_group;
+----+------------------+
| id | name             |
+----+------------------+
|  1 | Extras Group One |
+----+------------------+

mysql> select * from extras;
+----+---------+-------+---------------------+
| id | name    | price | timestamp           |
+----+---------+-------+---------------------+
|  1 | Extra 1 |  0.30 | 2011-10-08 18:57:55 |
|  2 | Extra 2 |  2.31 | 2011-10-08 18:58:10 |
+----+---------+-------+---------------------+

mysql> select * from extras_history;
+----+---------+-------+---------------------+
| id | name    | price | timestamp           |
+----+---------+-------+---------------------+
|  1 | Extra 1 |  0.30 | 2011-10-08 18:57:55 |
|  2 | Extra 2 |  2.30 | 2011-10-08 18:57:55 |
|  2 | Extra 2 |  2.31 | 2011-10-08 18:58:10 |
+----+---------+-------+---------------------+


mysql> select * from products_extras;
+----+------------+----------------+
| id | product_id | extra_group_id |
+----+------------+----------------+
|  1 |          2 |              1 |
+----+------------+----------------+
//This mean Product ID 2 have extras from  extra_group_id = 1

Order table for extras:

mysql> select * from order_products_extras;
+-------------------+----------+---------------------+
| order_products_id | extra_id | extra_timestamp     |
+-------------------+----------+---------------------+
|                 1 |        1 | 2011-10-08 18:57:55 |
|                 1 |        2 | 2011-10-08 18:58:10 |
+-------------------+----------+---------------------+

//Customer selected extra_id 1 and 2 from product_id 1

Use similar query like above to get the extra name and price from the extras_history table

Triggers:

CREATE TRIGGER `extras-afterinsert` AFTER INSERT ON `extras`
FOR EACH ROW BEGIN
    INSERT INTO `extras_history` VALUES (NEW.`id`, NEW.`name`, NEW.`price`, NEW.`timestamp`);
END
|
CREATE TRIGGER `extras-afterupdate` AFTER UPDATE ON `extras`
FOR EACH ROW BEGIN
    INSERT INTO `extras_history` VALUES (NEW.`id`, NEW.`name`, NEW.`price`, NEW.`timestamp`);
END

And similar for the products_history table.

Am I wasting my time using historical tables, should I just duplicate names/prices into order_products and order_products_extras tables ?

Note: There will be over 100,000 rows in the procucts and extras tables.. and over 1000 orders a day.

One danger with only recording a single timestamp is that it's hard to determine what the latest entry is for a given order. You have to use a subselect or a function. I'd recommending having start_timestamp and finish_timestamp, with the latter being NULL for the latest entry. When an entry becomes history, it gets its finish_timestamp filled in. There are other ways to deal with this problem, I'm sure, but this seems to me to be the most straightforward.

I really couldn't understand what you are trying to achieve with history tables. If the products price changes rapidly and you want to save the products base price at that current time why not put it into order_product ?

orders :
order_id / created_at / status (PK : order_id)

order_products : 
order_id / product_id / quantity /  product_price (PK : order_id, product_id)

this way you wouldn't have to join product_history every single time. you can do same denormalization for extras aswell.

P.S : try not to use reserved words like "order" in table / column names, it can introduce bugs.