表版本控制（历史表）和订单表关系 [英] Tables versioning (historical tables) and Order tables relationship
问题描述
如果我在 products 表中添加一个新产品或更改数据，它将自动添加到 products_history 。这是通过触发器完成的。
当客户下订单时，不需要在 order_products table。
要从 order_products 表中获取产品名称和价格， products_history 表不是产品表
注意：
查看以下表格和结果：
mysql>选择*从产品;
+ ---- + ------------ + ------- + ------------------- - +
| id |名称|价格| timestamp |
+ ---- + ------------ + ------- + ------------------- - +
| 1 |产品63 | 2.00 | 2011-10-08 18:55:53 |
| 2 |产品42 | 3.00 | 2011-10-08 18:55:44 |
+ ---- + ------------ + ------- + ------------------- - +
mysql>选择* from products_history;
+ ---- + ------------ + ------- + ------------------- - +
| id |名称|价格| timestamp |
+ ---- + ------------ + ------- + ------------------- - +
| 1 |产品23 | 2.00 | 2011-10-08 18:55:44 |
| 2 |产品42 | 3.00 | 2011-10-08 18:55:44 |
| 1 |产品63 | 2.00 | 2011-10-08 18:55:53 |
+ ---- + ------------ + ------- + ------------------- - +
订单表格：
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 |
+ ---- + ---------- + ------------ + ---------------- ----- +
从订单表中获取产品名称和价格：
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
结果：
+ ------------- + ------ + --------- --- + ------- + --------------------- +
| total_price | id |名称|价格| timestamp |
+ ------------- + ------ + ------------ + ------- + --- ------------------ +
| 9999.00 | 2 |产品42 | 3.00 | 2011-10-08 18:55:44 |
| 9999.00 | 1 |产品63 | 2.00 | 2011-10-08 18:55:53 |
+ ------------- + ------ + ------------ + ------- + --- ------------------ +
工作正常。
这个设计有什么重大缺陷，或者我可以采取不同的方法？
关于Extras的第二个问题...产品可以有附加功能或没有附加功能。我需要为 extra_group 表进行版本化吗？我只有版本为额外表。
请参阅下列表格：
mysql> select * from extra_group;
+ ---- + ------------------ +
| id |名称|
+ ---- + ------------------ +
| 1 |附加组一|
+ ---- + ------------------ +
mysql>选择* from extras;
+ ---- + --------- + ------- + --------------------- +
| id |名称|价格| timestamp |
+ ---- + --------- + ------- + --------------------- +
| 1 |额外1 | 0.30 | 2011-10-08 18:57:55 |
| 2 |额外2 | 2.31 | 2011-10-08 18:58:10 |
+ ---- + --------- + ------- + --------------------- +
mysql> select * from extras_history;
+ ---- + --------- + ------- + --------------------- +
| id |名称|价格| timestamp |
+ ---- + --------- + ------- + --------------------- +
| 1 |额外1 | 0.30 | 2011-10-08 18:57:55 |
| 2 |额外2 | 2.30 | 2011-10-08 18:57:55 |
| 2 |额外2 | 2.31 | 2011-10-08 18:58:10 |
+ ---- + --------- + ------- + --------------------- +
mysql>选择* from products_extras;
+ ---- + ------------ + ---------------- +
| id | product_id | extra_group_id |
+ ---- + ------------ + ---------------- +
| 1 | 2 | 1 |
+ ---- + ------------ + ---------------- +
//这意味着产品ID 2有extra_group_id = 1的附加功能
附加功能的订单表： p>
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 |
+ ------------------- + ---------- + -------------- ------- +
//客户从product_id 1中选择extra_id 1和2
使用类似上述的查询，从 extras_history 表中获得额外的名称和价格
触发器：
CREATE TRIGGER`extras-afterinsert` `extras`
每个ROW BEGIN
INSERT INTO`extras_history` VALUES（NEW.`id`，NEW.`name`，NEW.`price`，NEW.`timestamp`）;
END
|
CREATE TRIGGER`extras-afterupdate`在`extras`上更新后
用于每个ROW BEGIN
INSERT INTO`extras_history` VALUES（NEW.`id`，NEW.`name`，NEW。 `price`，NEW.`timestamp`）;
END
和 products_history table。
使用历史表浪费时间，我只需要将名称/价格重复到 order_products 和 order_products_extras 表？
注意：procuss和extras表中将有超过100,000行1000订单一天。
我真的不明白你试图用历史表达到什么。如果产品价格快速变化，并且您想要保存当前时间的产品基本价格，为什么不将其放入order_product？
订单：
order_id / created_at / status（PK：order_id）
order_products：
order_id / product_id / quantity / product_price（PK：order_id，product_id）
这样你不必每次都加入product_history。
PS：尽量不要在表/列名中使用保留字，比如order，它可以引入错误。
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 trigger.
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.
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.
这篇关于表版本控制（历史表）和订单表关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
