Need Guidance for designing database Table - (confuse for 1 column)

Question

I have two tables. 1.Products 2. Combo

Product Table has column like (product_id, product_name).

Combo Table has column like (combo_id, combo_name, combo_included_products /* combo might have 2 or more products from product table*/)

Rule: 1 Combo can have many products.

Product Table
product_id  product_name
1           Pen
2           Pencil
3           Pen Holders
4           Sharpeners

-

Combo Table     
combo_id    combo_name    this_combo_includes_below_products
1           Big_combo     (1,2,3,4) => big combo includes all products
2           Small_combo   (2,4) => this combo only includes product 2,4
3           test_combo    (1,2)
4           Other_combo   (1,4)

So How can I insert multiple products ids in third column of combo table?

I am thinking to store data like 1,2,3,4 and 2,4 and so on. Then problem will be to write join query. i.e

select combo.combo_id, combo.combo_name, product.product.id from combo join product ON combo.this_combo_included_products_id = product.product_id <= As there will be multiple product ids, It will not possible.

I am also thinking make a script where I will first fetch combo table as it is, then I will split third cloumn by "," and will run iterate (select * from combo where product id=this_combo_included_item_id[i]) <= I am not sure this is good idea however this can be a alternate solution which require some coding afterwards. (I use phpmysql to fetch data anyways - so I can process that after fetching.)

$sql = "SELECT *  FROM combo";
$result = $conn->query($sql);
while($row = $result->fetch_assoc()) {
  // I can run other query here
  $child_query = "select combo.combo_id, combo.combo_name, product.product.id from combo join product 
                 ON combo.this_combo_included_products_id = product.product_id";


}

However Is there anything else I can do while designing Database table. Thanks.

Read ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad?r=SearchResults&s=1|193.1585) and use a third table linking products and combos. — sticky bit, May 28 '20 at 23:15

GMB · Accepted Answer · 2020-05-29T00:24:38.707

Don't store multiple values in a single row. Don't store numbers as strings. The accepted answer to famous SO question gives great insights on how bad this is.

You have a many to many relationship between products and combos: each product may appear in may combos, and each combo may contain many products. From normalization perspective, the proper way to represent it is to create another table, called a bridge table, to store the relations.

create table product_combo (
    product_id int references product(product_id),
    combo_id int references combo(combo_id),
    primary key (product_id, combo_id)
);

For your sample data, the bridge table would contain:

product_id    combo_id
1             1
1             2
1             3
1             4
2             2
2             4
3             1
3             2
4             1
4             4

With this set-up in place, say that you want to select a given combo along with all its associated products, then you would go:

select c.*, p.*
from combos c
inner join product_combos pc on pc.combo_id = c.combo_id
inner join products p on p.product_id = pc.product_id
where c.combo_id = ?

And if you really want to, you can even rebuild the csv lists of products for each combo:

select c.combo_id, c.combo_name, group_concat(p.product_name) product_names
from products p
inner join product_combos pc on pc.product_id = p.product_id
inner jon combos c on c.combo_id = pc.combo_id
group by c.combo_id, c.combo_name

Need Guidance for designing database Table - (confuse for 1 column)

1 Answers1