2

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');
  • 1
    Note also that CONSTRAINT ... FOREIGN KEY ... doesn't actually do anything at all when ENGINE = MyISAM, since MyISAM doesn't actually support foreign key constraints. There is no reason anyone should be using MyISAM. – Michael - sqlbot Mar 25 '18 at 0:23
  • I thought MyISAM was supposed to be faster if the table is going to be read a lot more than it will be written to? Would it be appropriate for the reference table, which will hardly ever be changed? – BladePoint Mar 25 '18 at 0:28
2

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.

1

This is implementing an enumeration type: making sure that the value is only ever one from the specified list.

Some databases natively support enums: see https://www.postgresql.org/docs/current/static/datatype-enum.html for example.

Even why there is native support, you might still implement the behaviour using a table as in your example, so that the range of valid values can be altered later without a schema change.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.