Relational databases store information in table cells — in columns that describe aspects of an item and rows which tie together the columns. In a properly decomposed database design — one in which tables are created to reduce data redundancy and improve data integrity — tables must refer to one-another. Foreign keys enforce referential integrity.
This blog post walks through a use case, showing the evolution from initial idea to decomposed tables utilizing foreign keys.
A Back-of-a-Napkin Scribbled Version Initial Use Case
We’re developing an idea to track the handbags we sell. Our first pass at a table to hold the orders might be:
CREATE TABLE orders (
customerINTNOT NULL,
numberINTUNIQUE NOT NULL,
itemVARCHAR(99)NOT NULL
);
We may be in a rush, scribbling designs on napkins, but we remember to make the order number unique and each of the columns require input; we’re trying to avoid future problems. The table of our early sales looks like:
customer | number | item |
1 | 1234 | Hermès Fuchsia Diamond-Studded Birkin |
2 | 1235 | Hermès Niloticus Crocodile Himalaya Birkin |
3 | 1236 | Hermès Chaîne d’Ancre |
4 | 1237 | Hermès Niloticus Crocodile Himalaya Birkin |
5 | 1238 | Hermès Fuchsia Diamond-Studded Birkin |
Refinement #1 — Decomposing A Common Value
The above table shows duplication on the item names — like the 42 characters of "Hermès Niloticus Crocodile Himalaya Birkin
” — a duplication which will require more storage space as the sales mount and we copy those 42 characters again and again. Version 1 of our efforts at table decomposition moves the item description into its own table:
CREATE TABLE bags_version_1 (
id INT UNIQUE NOT NULL,
bag VARCHAR(99) UNIQUE NOT NULL
);
INSERT INTO bags_version_1
VALUES
( 9999, 'Hermès Blue Crocodile Birkin' ),
( 9998, 'Hermès Chaine d\’Ancre' ),
( 9997, 'Hermès Crocodile Exotic Skin Kelly' ),
( 9996, 'Hermès Fuchsia Diamond-Studded Birkin' ),
( 9995, 'Hermès Ginza Tanaka Crafted Birkin' ),
( 9994, 'Hermès Graphite Crocodile Birkin' ),
( 9993, 'Hermès Matte Birkin Crocodile' ),
( 9992, 'Hermès Niloticus Crocodile Himalaya Birkin' ),
( 9991, 'Hermès Rose Gold Kelly' ),
( 9990, 'Hermès Graphite Porosus Crocodile Birkin with Palladium Hardware' );
Let’s use that refinement to use a FOREIGN KEY
to link the orders and items together in the next version of our orders
table:
CREATE TABLE orders_version_2 (
customerINTNOT NULL,
numberINTUNIQUE NOT NULL,
itemINT,
FOREIGN KEY (item) REFERENCES bags_version_1(id)
);
Instead of duplicating the long text strings, the item column now REFERENCES
the id
column of the bags_version_1
table. The contents of our orders
table now looks like:
customer | number | item |
1 | 1234 | 9996 |
2 | 1235 | 9992 |
3 | 1236 | 9998 |
4 | 1237 | 9992 |
5 | 1238 | 9996 |
Recreate the original, easy-to-read orders table with a SQL Inner Join:
SELECT
customer,
number,
Bags_version_2.bag AS item
FROM
orders_version_2
INNER JOIN bags_version_2 ON
orders_version_2.item = bags_version_2.id ;
customer | number | item |
1 | 1234 | Hermès Fuchsia Diamond-Studded Birkin |
2 | 1235 | Hermès Niloticus Crocodile Himalaya Birkin |
3 | 1236 | Hermès Chaîne d’Ancre |
4 | 1237 | Hermès Niloticus Crocodile Himalaya Birkin |
5 | 1238 | Hermès Fuchsia Diamond-Studded Birkin |
Refinement #2 — Decomposing All The Common Values
A further refinement would be to decompose all the common values, leading to tables of makers
and the stylistic bag families
:
CREATE TABLE makers (
id INT UNIQUE NOT NULL,
name VARCHAR(22) UNIQUE NOT NULL
) ;
INSERT INTO makers
VALUES
( 1, 'Hermès' ),
( 2, 'Judith Leiber' ),
( 3, 'Mouawad' );
CREATE TABLE families (
id INT UNIQUE NOT NULL,
name VARCHAR(33) UNIQUE NOT NULL
) ;
INSERT INTO families
VALUES
( 0, '' ),
( 10, 'Birkin' ),
( 11, 'Kelly' );
The next version of our bags
table then references the above tables:
CREATE TABLE bags_version_3 (
id INT UNIQUE NOT NULL,
maker INT NOT NULL,
family INT,
bag VARCHAR(99) UNIQUE NOT NULL,
FOREIGN KEY (maker) REFERENCES makers(id),
FOREIGN KEY (family) REFERENCES families(id)
);
INSERT INTO bags_version_3
VALUES
( 9999, 1, 10, 'Blue Crocodile' ),
( 9998, 1, 0, 'Chaine d\’Ancre' ),
( 9997, 1, 11, 'Crocodile Exotic Skin' ),
( 9996, 1, 10, 'Fuchsia Diamond-Studded' ),
( 9995, 1, 10, 'Ginza Tanaka Crafted' ),
( 9994, 1, 10, 'Graphite Crocodile' ),
( 9993, 1, 10, 'Matte Crocodile' ),
( 9992, 1, 10, 'Niloticus Crocodile Himalaya' ),
( 9991, 1, 11, 'Rose Gold' ),
( 9990, 1, 10, 'Graphite Porosus Crocodile with Palladium Hardware' );
Recreate the original orders table with SQL which uses the several FOREIGN KEY
references found in the bags_version_3
table, above:
SELECT
customer,
number,
makers.name AS maker,
families.name AS family,
bags_version_3.bag AS item
FROM
orders_version_2
INNER JOIN bags_version_3 ON
orders_version_2.item = bags_version_3.id
INNER JOIN makers ON
makers.id = bags_version_3.maker
INNER JOIN families ON
families.id = bags_version_3.family
ORDER BY customer ;
This decomposition gives the flexibility of extracting data by maker and bag family, which will be useful when generating statistics on the ever-growing business to forecast fortuitous purchases.
One limitation of the schema developed here is that the bag family table is a single flat namespace. Should two makers wind up using the same family name a collision will happen. This limitation would be resolved in a more mature software solution.
Conclusion
In this blog post we’ve covered:
- the basics of using a
FOREIGN KEY
to enforce data integrity between database tables - the software development refinement process of table decomposition (which leverages the
FOREIGN KEY
s to enforce the relationships between tables) - the technique of nesting
INNER JOIN
s to create simple tables from complex, related tables
To learn more about SQL, check out other SQL blog posts and enroll in our SQL Nanodegree program.
SQL Sample Table Code
The following will build and populate the sample data referred to in this blog post. Test SQL interactively at SQL Fiddle or ExtendsClass.
CREATE TABLE orders (
customerINTNOT NULL,
numberINTUNIQUE NOT NULL,
itemVARCHAR(99)NOT NULL
);
INSERT INTO orders
VALUES
( 1, 1234, 'Hermès Fuchsia Diamond-Studded Birkin' ),
( 2, 1235, 'Hermès Niloticus Crocodile Himalaya Birkin' ),
( 3, 1236, 'Hermès Chaîne d\'Ancre' ),
( 4, 1237, 'Hermès Niloticus Crocodile Himalaya Birkin' ),
( 5, 1238, 'Hermès Fuchsia Diamond-Studded Birkin' );
CREATE TABLE bags_version_2 (
id INT UNIQUE NOT NULL,
bag VARCHAR(99) UNIQUE NOT NULL
);
INSERT INTO bags_version_2
VALUES
( 9999, 'Hermès Blue Crocodile Birkin' ),
( 9998, 'Hermès Chaine d\’Ancre' ),
( 9997, 'Hermès Crocodile Exotic Skin Kelly' ),
( 9996, 'Hermès Fuchsia Diamond-Studded Birkin' ),
( 9995, 'Hermès Ginza Tanaka Crafted Birkin' ),
( 9994, 'Hermès Graphite Crocodile Birkin' ),
( 9993, 'Hermès Matte Birkin Crocodile' ),
( 9992, 'Hermès Niloticus Crocodile Himalaya Birkin' ),
( 9991, 'Hermès Rose Gold Kelly' ),
( 9990, 'Hermès Graphite Porosus Crocodile Birkin with Palladium Hardware' );
CREATE TABLE orders_version_3 (
customerINTNOT NULL,
numberINTUNIQUE NOT NULL,
itemINT,
FOREIGN KEY (item) REFERENCES bags_version_1(id)
);
INSERT INTO orders_version_3
VALUES
( 1, 1234, 9996 ),
( 2, 1235, 9992 ),
( 3, 1236, 9998 ),
( 4, 1237, 9992 ),
( 5, 1238, 9996 );
CREATE TABLE makers (
id INT UNIQUE NOT NULL,
name VARCHAR(22) UNIQUE NOT NULL
) ;
INSERT INTO makers
VALUES
( 1, 'Hermès' ),
( 2, 'Judith Leiber' ),
( 3, 'Mouawad' );
CREATE TABLE hermes_families (
id INT UNIQUE NOT NULL,
name VARCHAR(33) UNIQUE NOT NULL
) ;
INSERT INTO hermes_families
VALUES
( 10, 'Birkin' ),
( 11, 'Kelly' );
CREATE TABLE bags_version_3 (
id INT UNIQUE NOT NULL,
maker INT NOT NULL,
bag VARCHAR(99) UNIQUE NOT NULL,
FOREIGN KEY (maker) REFERENCES makers(id)
);
INSERT INTO bags_version_3
VALUES
( 9999, 1, 'Blue Crocodile Birkin' ),
( 9998, 1, 'Chaine d\’Ancre' ),
( 9997, 1, 'Crocodile Exotic Skin Kelly' ),
( 9996, 1, 'Fuchsia Diamond-Studded Birkin' ),
( 9995, 1, 'Ginza Tanaka Crafted Birkin' ),
( 9994, 1, 'Graphite Crocodile Birkin' ),
( 9993, 1, 'Matte Birkin Crocodile' ),
( 9992, 1, 'Niloticus Crocodile Himalaya Birkin' ),
( 9991, 1, 'Rose Gold Kelly' ),
( 9990, 1, 'Graphite Porosus Crocodile Birkin with Palladium Hardware' );
CREATE TABLE orders_version_2 (
customerINTNOT NULL,
numberINTUNIQUE NOT NULL,
itemINT,
FOREIGN KEY (item) REFERENCES bags_version_2(id)
);
INSERT INTO orders_version_2
VALUES
( 1, 1234, 9996 ),
( 2, 1235, 9992 ),
( 3, 1236, 9998 ),
( 4, 1237, 9992 ),
( 5, 1238, 9996 );