Database Layout (Product Catalogue)

[completeamateur]

Hi guys,

 

I'm setting up a database to help control my web site's product catalogue and stock levels.

 

I'm thinking of having 2 tables, one that describes each product (common information), as well as one that contains possible options (unique information).

 

I wanted to hear people's suggestions regarding the layout I have come up with...

 

CREATE TABLE IF NOT EXISTS `products` (
  `productID` smallint(6) unsigned NOT NULL auto_increment,
  `team` text NOT NULL,
  `description` text NOT NULL,
  `HA3` tinyint(1) NOT NULL,
  `gk` tinyint(1) NOT NULL,
  `start` year(4) NOT NULL,
  `end` year(4) NOT NULL,
  PRIMARY KEY  (`productID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `stock` (
  `stockID` smallint(5) unsigned NOT NULL auto_increment,
  `size` tinytext NOT NULL,
  `price` float NOT NULL,
  `condition` tinyint(1) NOT NULL,
  `qty` smallint(3) NOT NULL,
  `productID` smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (`stockID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

FYI, each product could have up to 50 different options (approx. 10 size variations and 5 condition variations).  Is this the best approach or would you approach it differently?  It seems a little cumbersome but my brain isn't capable of anything better!

 

Thanks for your help.