Mysql child table to represent alternative value t

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.

Answer 1

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.