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 SELECT
s and when INSERT
ing 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?”