Pros and Cons of Descriptive Foreign Keys?

Given the following scenario:

Addresses
name group city
Johnny Friends Cambridge
Kevin Friends Allston
Justin Coworkers Easton
Aya Coworkers North Andover
Josh Family Groton
Connie Family Easton

In a database, that could be represented as such:

CREATE TABLE GroupNum (
groupId TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
groupName VARCHAR(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE AddressNum (
firstName VARCHAR(20),
groupId TINYINT UNSIGNED NOT NULL,
city VARCHAR(20),
KEY (groupId),
FOREIGN KEY (groupId) REFERENCES GroupNum (groupId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO GroupNum (groupName) VALUES ('Friends'),('Coworkers'),('Family');
INSERT INTO AddressNum (firstName,groupId,city) VALUES
('Johnny',1,'Cambridge'),
('Kevin',1,'Allston'),
('Justin',2,'Easton'),
('Aya',2,'North Andover'),
('Josh',3,'Groton'),
('Connie',3,'Easton');

SELECT firstName,groupName,city
FROM AddressNum INNER JOIN GroupNum USING (groupId);

firstName groupName city
Johnny Friends Cambridge
Kevin Friends Allston
Justin Coworkers Easton
Aya Coworkers North Andover
Josh Family Groton
Connie Family Easton

or, using descriptive foreign keys:

CREATE TABLE GroupText (
groupName VARCHAR(20) NOT NULL PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE AddressText (
firstName VARCHAR(20),
groupName VARCHAR(20) NOT NULL,
city VARCHAR(20),
KEY (groupName),
FOREIGN KEY (groupName) REFERENCES GroupText (groupName)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO GroupText (groupName) VALUES ('Friends'),('Coworkers'),('Family');
INSERT INTO AddressText (firstName,groupName,city) VALUES
('Johnny','Friends','Cambridge'),
('Kevin','Friends','Allston'),
('Justin','Coworkers','Easton'),
('Aya','Coworkers','North Andover'),
('Josh','Family','Groton'),
('Connie','Family','Easton');

SELECT firstName,groupName,city
FROM AddressText;

firstName groupName city
Johnny Friends Cambridge
Kevin Friends Allston
Justin Coworkers Easton
Aya Coworkers North Andover
Josh Family Groton
Connie Family Easton

So what are the pros and cons of using descriptive foreign keys, as opposed to surrogate keys?

Con:
1) An auto-increment surrogate key (a number) is referenced faster than strings, so INSERTS will be slightly slower.
2) Data redundancy. Foreign keys need to be updated (and/or deleted) whenever the data is updated.

Pro:
1) Using a surrogate key means that to get user-readable data (ie, the text) a JOIN must be made. Using a descriptive foreign key, no JOIN needs to be made to get user-readable data. This includes reading with SELECTs and when INSERTing or modifying data. For example, to insert into AddressNum the groupId is needed.
2) Updates/deletes on a foreign key can be set to cascade.
3) Human-readable data in the database. This will make it easier for the DBA to debug data issues, as they can use a SHOW CREATE TABLE to find the foreign key references, and then get a sample of data from one table and understand the scenario, without having to JOIN.

My thinking right now is that I would use a descriptive foreign key where the forign key is “static” data, or the foreign key references are used in a small number of tables.

For instance, in a user-based application, I would not use “username” as a descriptive foreign key, I would stick with a surrogate key of “uid”, because the foreign key is ubiquitous, and if a user changed their username, every single table would need to cascade — or else the update would fail.

However, I would use descriptive foreign keys for “static” data like locale (language) data, country and state names, because they likely will not change, even though they might affect many changes. Athough note that in these places, I’m also using standards — “locale” for language, ISO country and state/province codes, etc.

I would also use it in the scenario above, where the data is not static, but also does not touch many tables — the ‘group name’ only affects the ‘listings’ table if it gets changed.

So, what are the pros and cons of this method? When would you and would you not use it?

15 responses to “Pros and Cons of Descriptive Foreign Keys?