Mysql – Reason for having additional “type” table of all possible string values

database-designMySQLschema

I've been looking over some sample schemas, and here's something I've seen done several times, but being new to MySQL I'm not sure why. Say I've got a table for items:

CREATE TABLE item (
  itemID CHAR(5) NOT NULL,
  name VARCHAR(255) NOT NULL,
  itemType VARCHAR(255) NOT NULL,
  PRIMARY KEY (itemID),
  CONSTRAINT fk_item_itemType FOREIGN KEY (itemType) REFERENCES itemType(name)
) ENGINE = MyISAM;

Why would you need itemType to be a foreign key to another table that lists all the possible item types? Would it prevent typos if the entered value wasn't in the other table? The other table is this:

CREATE TABLE itemType (
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (name),
) ENGINE = MyISAM;
INSERT INTO itemType (name) VALUES ('gloves');
INSERT INTO itemType (name) VALUES ('boots');
INSERT INTO itemType (name) VALUES ('ring');

Best Answer

It seems the idea here is to restrict what itemTypes can be used in the item table to only those in the itemType table, while at the same time avoiding having to do a JOIN with the itemType table to get the actual value whenever you SELECT from the item table. It's a kind of denormalization in order to optimize SELECT statements. This comes at a cost of needing more storage space since every item record will have the full itemType name, potentially as long as 255 characters.

With a normalized approach you would have tables defined like this:

CREATE TABLE item (
  ...
  itemTypeID int unsigned NOT NULL,
  ...
  CONSTRAINT fk_item_itemType FOREIGN KEY (itemTypeID) REFERENCES itemType(itemTypeID)
) ENGINE = MyISAM;

CREATE TABLE itemType (
  itemTypeID int unsigned NOT NULL,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (itemTypeID),
) ENGINE = MyISAM;

However, when SELECTing from the item table you would now have to do a JOIN in order to display the itemType:

SELECT i.itemID, i.name, it.name "itemTypeName"
FROM item i 
  INNER JOIN itemType it USING (itemTypeID);

Another solution to this is using an ENUM and avoid the itemType table altogether:

CREATE TABLE item (
  itemID CHAR(5) NOT NULL,
  name VARCHAR(255) NOT NULL,
  itemType ENUM('gloves', 'boots', 'ring') NOT NULL,
  PRIMARY KEY (itemID)
) ENGINE = MyISAM;

The drawback of this approach is that you would have to ALTER the table whenever you want to add a new itemType.

Related Question