Sql-server – How to create a table to lookup values for combinations of varying sized sets of numbers

sql-server-2012

I have a table of products, each with it's own id (int) as primary key:

product_id  name
3           product1
4           product2
6           product3
10          product4

I have a table of packaging types, each with it's own id (int) as primary key that these products may be packaged/shipped in:

packaging_id   name
15             Box
20             Bag
21             Padded Mailer
26             Tube

I need a table(s) to lookup which packaging to use for a given combination of products. For example, product_ids 3 and 6 will fit together in a bag (package_id 20). Likewise, product_ids 3, 4, and 10 may require a Box (packaging_id 15). The combination may be any number of product_ids. A pseudo query would look like this:

SELECT packaging_id FROM lookuptable WHERE product_id = 3 AND product_id = 6

In other words, I must be able to query a specific combination of n product_ids and retrieve a packaging_id. Something like this would work if the number of product_ids in the WHERE clause was fixed, e.g. only two products could ever fit in a package:

product_id1   product_id2  packaging id
3             6            20

However, there may be n product_ids to query, so this won't work.

Another solution may be to create some type of hash for the primary key, like this:

product_combination_id   packaging_id
00030006                 20

But I feel like there is a more robust solution. Ideas?

Thanks,
Casey

Best Answer

I'll try with a table structure this way:

create table packaging_group
(
    pack_group_id int primary key,
    product_id int foreign key references products(product_id),
    packaging_id int foreign key references packages(package_id)
);

No matter how many groups and how many products fit a package. You can always add/remove products to an specific package.

Related Question