Mysql子表表示父表中关系键的替代值 [英] Mysql child table to represent alternative value to relational key in parent table

问题描述

不太确定标题解释了它..

Not quite sure that title explains it..

这是我的问题.我有一张代表产品的表.这些产品是按月订阅的一部分，这些产品组合在一个盒子中.在结账时，一些产品也可以作为插件"单独添加到订单中.例如，订阅 A 附带一个装有产品 1、2、3、3 的盒子，但您可以根据需要添加任意数量的额外 3 和 4，因为它们也是插件.

Here my problem. I have an table that represents products. These products are part of a monthly subscription that come grouped in a box. At checkout some of the products can also be added in individually to the order as "addons". Example, subscription A comes with a box with products 1,2,3,3 in it but you can add as many extra 3 and 4 as you like since they are also addons.

我表示这些数据的解决方案是有一个 products 表，然后是一个 products_addons 表，它只存储产品的 id.请参阅下面的布局.

My solutions for representing this data is to have a products table and then a products_addons table that just stores the id of the product. See the layout below.

products
id, name, price

products_addons
product_id

这样我就可以加入表格并查看哪个产品也是插件，所以在我的 id 为 3 和 4 的示例产品将保存到 product_addons 表中.这似乎非常低效，我想知道是否有更好的方法去解决这个问题?我正在考虑 products 表 is_also_addon 中的 bool 字段，但这似乎也效率低下.

This way I can join the tables and see which product is also an addon, so in my example products with id 3 and 4 would be saved into the product_addons table. This seems very inefficient and I'm wondering if there is a better way to go about this? I was thinking about a bool field in the products table is_also_addon but this also seems inefficient.

推荐答案

这里是一个快速思考的内容，以及我在 该链接中的连接表.

Here is a quick take to ponder, as well as my write-up on Junction Tables in that link.

-- drop table products;
create table products
(   prodId int auto_increment primary key,  -- sku, code, whatever
    isAssembly int not null, -- bool, whatever, for quick retrieval of just them
    descr varchar(255) not null,
    price decimal(10,2) not null -- varies here over time, but not in orderLines (frozen there)
);

-- drop table assemblies;
create table assemblies
(   -- this assemblies table is to give a description, and to be one of the two anchors to the Junction table
    -- but Orders still emanate from the products table
    ashId int auto_increment primary key,   -- trying to keep the column name clean
    descr varchar(255) not null -- 'October Chocolate Package'
);

-- drop table orders;
create table orders
(   ordId int auto_increment primary key,
    ordDate datetime not null
    -- etc customer blah blah blah
);

-- drop table orderLines;
create table orderLines
(   id int auto_increment primary key,
    ordId int not null,
    prodId int not null,    -- a product. Period. Can be an assembled product or not
    seq int not null,
    qty int not null,
    price decimal(10,2) not null, -- the frozen price upon placing the order
    CONSTRAINT fk_ol_orders FOREIGN KEY (ordId) REFERENCES orders(ordId),
    CONSTRAINT fk_ol_products FOREIGN KEY (prodId) REFERENCES products(prodId)
);

-- drop table paJunction;
create table paJunction
(   -- product/assembly junction table
    -- one row here for each product that is in an assembly
    id int auto_increment primary key,
    prodId int not null,
    ashId int not null,
    qty int not null,   -- how many prods go in that assembly
    unique key(prodId,ashId),   -- no dupes allowed
    unique key(ashId,prodId),   -- no dupes allowed
    CONSTRAINT fk_paJ_products FOREIGN KEY (prodId) REFERENCES products(prodId),
    CONSTRAINT fk_paJ_assemblies FOREIGN KEY (ashId) REFERENCES assemblies(ashId)
);

通过创建新程序集，您可以非常灵活地对每月的包(或程序集)进行逐月调整.并允许重复使用您想要以最小的努力再次宣传的旧版本.

It would give you great flexibility in having month-over-month tweaks to your packages (or assemblies) of the month with new assemblies created. And allowing for re-use of old ones you want to promote as oh so special again with minimal effort.

维护定价历史.

允许用户将他们想要的任何东西放入购物车.

Allows the user to put whatever they want in the shopping cart.

我敢肯定，对于一些看到这一点的人来说，程序集需要视觉效果.我可以举几个例子.

I am sure sure the assemblies would need a visual for some people seeing this. I could put a few examples together.

主要要点是使用 Junction 表，并在产品表上订购.

The main takeaways are the use of Junction tables, and ordering out of on products table.

这篇关于Mysql子表表示父表中关系键的替代值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！