Craig Craig - 5 months ago 11
SQL Question

Data Structure, don't want to store a list as text

All -
I need some help designing a table for a Postgre SQL database. I have a table of products ie;

CREATE TABLE products (
product_id integer PRIMARY KEY,
product_name text,
price numeric);

INSERT INTO products (product_id, product_name, price) VALUES
(DEFAULT, 'Purple Widget', '5.50'),
(DEFAULT, 'Green Widget', '1.50'),
(DEFAULT, 'Yellow Widget', '5.50'),
(DEFAULT, 'Spotted Widget', '6.50'),
(DEFAULT, 'Extra Large Purple Widget', '102.50'),
(DEFAULT, 'Extra Large Spotted Widget', '101.50');


I want to start selling a package of products (multiple products, one price). I am trying to figure out a structure for a packages table. Two options I don't like are -

CREATE TABLE packages (
package_id integer PRIMARY KEY,
package_name text,
package_products varchar(50),
price numeric);

INSERT INTO packages (package_id, package_name, package_products, price) VALUES
(DEFAULT, 'Small Widgets', '0,1,2,3', '6.25'),
(DEFAULT, 'Large Widgets', '5,6', '200.00');


or

CREATE TABLE packages (
package_id integer PRIMARY KEY,
package_name text,
product_id1 integer,
product_id2 integer,
product_id3 integer,
product_id4 integer,
product_id5 integer,
price numeric);

INSERT INTO packages (package_id, package_name, product_id1, product_id2,
product_id3, product_id4, product_id5, price) VALUES
(DEFAULT, 'Small Widgets', '0', '1' ,'2', '3', NULL, '6.25'),
(DEFAULT, 'Large Widgets', '5', '6', NULL, NULL, NULL, '200.00');


I can't move the packages logic to the products table because each product could be in multiple packages. Both of those options above require a lot of work at the web layer to make work, plus they both seem really inefficient.

Ideas? Thanks in advance!

Answer

This is a standard many-to-many relationship (many packages to many items)

CREATE TABLE products (
product_id integer PRIMARY KEY,
product_name text,
price numeric);

CREATE TABLE packages (
package_id integer PRIMARY KEY,
package_name text,
price numeric); 

CREATE TABLE package_items (
package_item_id integer PRIMARY KEY,
package_id integer,
product_id integer,
qty integer );