How to choose my primary keys?

Databases
1

Hi all,

I’m working on a webshop system where product and category data of multiple shops are kept in these tables:

CREATE TABLE `category` (
  `categoryID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shopID` int(10) unsigned NOT NULL,
  `parentID` int(10) unsigned NOT NULL,
  `title` varchar(100) NOT NULL,
  `urlTitle` varchar(100) NOT NULL,
  `description` varchar(255) NOT NULL,
  `path` varchar(255) NOT NULL,
  `order` int(10) unsigned NOT NULL,
  PRIMARY KEY (`categoryID`),
  UNIQUE KEY `path` (`path`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=34 ;

CREATE TABLE `product` (
  `productID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `shopID` int(10) unsigned NOT NULL,
  `catalogueID` int(10) unsigned NOT NULL,
  `price` float unsigned NOT NULL,
  PRIMARY KEY (`productID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ;

CREATE TABLE `product_category` (
  `productID` int(10) unsigned NOT NULL,
  `categoryID` int(10) unsigned NOT NULL,
  `order` int(10) unsigned NOT NULL,
  PRIMARY KEY (`productID`,`categoryID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Products can be assigned to multiple categories.

categoryID and productID are both PRIMARY keys and shopID is a foreign key for the product and category table.

I’m not sure why, but suddenly I started doubting that maybe this was a better solution:

Take shopID and productID as a compound PRIMARY key and make productID auto-increment. Do the same for the category table, but with categoryID. This way, productID and categoryID auto-increment per shopID. Lastly, add shopID to the product_category table and make a compound PRIMARY key with shopID, productID and categoryID.

I’m clearly missing some fundamental knowledge and experience in database design here :wink: Can somebody point out to me what is the best approach and why? For starters, I think it’s better to have shopID in every table, because you can directly query for all results in a given shop…

This is what the new db scheme would look like:

CREATE TABLE `category` (
  `shopID` int(10) unsigned NOT NULL,
  `categoryID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parentID` int(10) unsigned NOT NULL,
  `title` varchar(100) NOT NULL,
  `urlTitle` varchar(100) NOT NULL,
  `description` varchar(255) NOT NULL,
  `path` varchar(255) NOT NULL,
  `order` int(10) unsigned NOT NULL,
  PRIMARY KEY (`shopID`,`categoryID`),
  UNIQUE KEY `path` (`path`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

CREATE TABLE `product` (
  `shopID` int(10) unsigned NOT NULL,
  `productID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `catalogueID` int(10) unsigned NOT NULL,
  `price` float unsigned NOT NULL,
  PRIMARY KEY (`shopID`,`productID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ;

CREATE TABLE `product_category` (
  `shopID` int(10) unsigned NOT NULL,
  `productID` int(10) unsigned NOT NULL,
  `categoryID` int(10) unsigned NOT NULL,
  `order` int(10) unsigned NOT NULL,
  PRIMARY KEY (`shopID`,`productID`,`categoryID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Grtz, M.

2

it depends on whether you want to re-use category numbers and/or product numbers across shops – personally, i don’t think it buys you anything, and it introduces needless complexity

carrying the shop_id down in the product_category table is also not necessary because it’s in both the category and product tables, and i will go out on a limb here and say that you will never need to query the product_category table by itself without at the same time joining to either the product table or the category table, so the redundant shop_id only introduces an opportunity for data inconsistency if you accidentally put the wrong shop

3

Tnx for your reply.
That clears some things up for me!