Wednesday, July 6, 2011

Database Primary Key Naming Conventions

Train couplingThis 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

No comments: