Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011
    Posts
    1

    Unanswered: which datatype to use

    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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •