Welcome to Julian Kuiters Saturday, August 18 2018 @ 05:10 PM AEST

Column Confusion

  • Contributed by:
Database Design - Don't start column names with FK_
- Always name a FK column the same as the PK

I happened to glance a database model today that had a column naming scheme that scared me: column names that started with FK_.

I'm pretty sure the designer was intending to show that the column was actually used as part of a Foreign Key (FK), however the only object names that should start with FK_ are infact foreign keys. Having other types of objects starting with FK_ could create a lot of confusion, especially for developers who might be left wondering if they are supposed to populate this column or not.

This led directly into the next thing I noticed in the diagram: columns being renamed across tables. I'm talking about a Primary Key (PK) column in tableA called TableAID being used as the FK in tableB, but being called LinkToTableAPK. This makes it hard for someone glancing the database diagram to see the relationship between these columns. Whatever the column is named in TableA, it should be called in TableB.

Wherever you need to repeat or copy a column from one table to the next you should use the same name. Unless of course the data type is different, or it's meaning is different: in which case it's not the same column anyway.

Keeping the names the same, and not using FK_ in column names will keep your database diagram simple and easy to read.