Database Primary Key Naming Conventions

This post specifically deals with the two naming conventions of a primary key for example “Id” or “<tablename>Id".

Using the convention including the tablename prepended to “Id” makes creating and reading SQL query joins more intuitively readable as in:

SELECT a.AuthorId

    , a.AuthorName

    , b.BookId

    , b.BookName 

FROM Author a

   INNER JOIN Book b 

   ON b.AuthorId = a.AuthorId

Versus the alternative using a standard key name, such as “Id" for all tables, as in the following:

SELECT a.Id AS AuthorId

    , a.AuthorName

    , b.Id AS BookId

    , b.BookName 

FROM Author a

   INNER JOIN Book b 

   ON b.AuthorId = a.Id

However it adds additional abstraction/mapping complexity when using a generic Repository pattern* that uses a standard primary key property name for the Repository base classes and interfaces.

*The design is possibly more correctly labeled as a DAO pattern depending on the specific implementation.

photo courtesy: amboo who? / CC BY-SA 2.0