which datatype to use

zr1skydiver · #1 (**permalink**) 08-18-11, 15:36

I have two tables. One is products and the other is product_types. The relationship between the twp has always been 1 to 1. I just found out that a product can now fall under several datatypes. What datatype would I use so that product_type_id in products could contain the values 1,2, and 3?

r937 · #2 (**permalink**) 08-18-11, 17:04

you seriously do not want to do it that way, you're asking for a world of hurt

you need a third table ...

Code:

CREATE TABLE product_producttypes
( product_id INTEGER NOT NULL 
, product_type_id INTEGER NOT NULL 
, PRIMARY KEY ( product_id, product_type_id )
);
INSERT INTO product_producttypes VALUES
 ( 427 , 1 )
,( 427 , 3 )
,( 427 , 4 )
,( 428 , 2 )
,( 429 , 1 )
,( 429 , 3 )

one row per product per product_type

and then remove the product_type_id column from the products table

Internet Services

Web Development

Digital Marketing

Consumer Tech