Mysql child table to represent alternative value to relational key in parent table

Question

Not quite sure that title explains it..

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.

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

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.

how is it any different than having a products table with these products: car, muffler ? If they want 3 mufflers, whatever blows their hair back, let em. Orderline (detail): 1 car, 2 mufflers. They get their 3 mufflers, indirectly. — Drew, Sep 18 '15 at 23:49
Because a box in a subscription will always have base products. The addons will change from month to month during the subscription, meaning "for my next delivery I want to remove x addon and add y addon". Its all based on inventory in house if that makes sense. — Naterade, Sep 18 '15 at 23:51
make it a new product, then in Feb 2016 we you loved the July 2015 one (and they forgot about it), your work is simpler. There is nothing saying you have to give them the same assembly (box) every month. Just re-point. And if you never want to re-use the old one, who cares, don't. At least you have historical data for the joins to figure out what it was. — Drew, Sep 18 '15 at 23:56
The only issue there is some of the base products are also addons and the skus are the same. It would be 2 duplicate rows with a differentiator. — Naterade, Sep 18 '15 at 23:57
you are saying you have sku's of atoms that have the same sku's of molecules ? You would have to show some sample data — Drew, Sep 18 '15 at 23:58
if you can't do what I explained that is in my head, perhaps and probably not communicated, or was communicated/understood but won't work, then you have a data problem — Drew, Sep 19 '15 at 00:00
You may be right. I think the simplest way is to add an "is_available_as_addon" field to the product table so its available to add in a cart dropdown. — Naterade, Sep 19 '15 at 00:02
prefer adding rows over adding columns. prefer adding columns over adding tables.... don't split products over two tables. Add a column to product to indicate/control behaviour — Bohemian, Sep 19 '15 at 00:32

score 1 · Accepted Answer · edited May 23 '17 at 12:29

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.

Pricing history is maintained.

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.

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

Mysql child table to represent alternative value to relational key in parent table

1 Answers1

Linked