SQL Foreign Keys Should Only Relate To One Table

Photo: Caspar Camille Rubin

In my last post, I covered why storing enumerations in your database was the wrong thing to do. The piece of work that came to me as a Pull Request that generated that post was a PR that kept giving. This post is based on a second issue I encountered in that review and why it is also the wrong thing to do.

In a database schema, a Foreign Key should only connect two tables.

The change in the PR was implementing an audit table for some notifications that are sent. The audit table was to store an Id for the sent notification, an Id for the Subscriber that viewed the notification and an Id for some content sent with the notification. All very simple. Only I could see that there was no Foreign Key defined for the SubscriberId Column. I tried to create a simple query to join to the subscriber’s table from the SuscriberId. It turned out that a subscriber table had not been created. Instead, the SubscriberId was either:

• The Id for a fully signed up User (the projects user data is split between Customer and Supplier user data)
• The Id for an Unknown Subscriber, someone that was not a customer but who could receive the notification.

I asked the original developer to write a query that would allow me to view the opened notification audit data. I did this to illustrate that following this approach of not using a Foreign Key would make for a very hard to maintain data schema. The reason they thought this was a good idea was that they are storing the SubscriberType which was an enumeration in the database. The query I was sent back was like the following:

SELECT    *
FROM      AuditTable Join To CustomerTable
WHERE     SubscriberType = 1
UNION
SELECT    *
FROM      AuditTable Join To SupplierTable
WHERE     SubscriberType = 2
UNION
SELECT    *
FROM      AuditTable Join To UnknownSubscriberTable
WHERE     SubscriberType = 3

Wow. So much confusion and hard work. I always have found that if I have a UNION in a query I have almost certainly got my schema or design wrong somewhere.

There was a clear need to clean this schema up. Remember

A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.

By creating a Subscription table and accumulating known and unknown subscriber data we can then add a Foreign Key for the audit table. The table contains data specific to the subscriber and is now managed through the subscription process. While there are some duplicate data fields at present this is a temporary step until we clean up the customer and supplier data into a single user data table. At that point, we will remove the duplicate fields and use further Foreign Keys.

Thinking about your schema is vital as you develop your application. While you may find you adapt it as you build functionality, seeing clear relationships and dependencies is key to underpinning clean code. I have taken on projects in which the database has no Foreign Keys and they are always more difficult to maintain and enhance. You will benefit from not trying to save time in developing one feature as it will almost always make dependant changes or requirements harder to implement.

If you have any comments or thoughts around this post then please contact me via twitter or email.