CodingForums.com - View Single Post

Old Pedant · 11-07-2012, 08:09 PM

Like this:

Code:

CREATE TABLE products ( 
    productID INT AUTO_INCREMENT PRIMARY KEY,
   product_name VARCHAR(40),
   product_details TEXT, 
   product_price DECIMAL 11,2) 
) ENGINE=INNODB;

CREATE TABLE product_images (
    imgID INT AUTO_INCREMENT PRIMARY KEY,
    productID INT,
    imgName VARCHAR(50),
    CONSTRAINT FOREIGN KEY productID REFERENCES products(productID)
) ENGINE=INNODB;

It's called a "one to many table". One productID can have many imgID records.

It is *VERY VERY* standard database design. Read up on NORMALIZATION.

No, it doesn't lead to "very complicated SQL statements." It leads to quite common JOINs.

11-07-2012, 08:09 PM	PM User \| #11
Old Pedant Supreme Master coder! Join Date: Feb 2009 Posts: 23,172 Thanks: 59 Thanked 3,993 Times in 3,962 Posts	Like this: Code: CREATE TABLE products ( productID INT AUTO_INCREMENT PRIMARY KEY, product_name VARCHAR(40), product_details TEXT, product_price DECIMAL 11,2) ) ENGINE=INNODB; CREATE TABLE product_images ( imgID INT AUTO_INCREMENT PRIMARY KEY, productID INT, imgName VARCHAR(50), CONSTRAINT FOREIGN KEY productID REFERENCES products(productID) ) ENGINE=INNODB; It's called a "one to many table". One productID can have many imgID records. It is VERY VERY standard database design. Read up on NORMALIZATION. No, it doesn't lead to "very complicated SQL statements." It leads to quite common JOINs. __________________ An optimist sees the glass as half full. A pessimist sees the glass as half empty. A realist drinks it no matter how much there is.