Using "Id" as Primary Key in green field Dapper Contrib MVC 5 site

Question

I am creating a new site and will base it on ASP.NET MVC 5 & Dapper Contrib. I want to go all in with all these technologies - and get all the time saving / convention over configuration / terse code advantages available.

I am designing the database in SQL Server 2014. Historically I have always named primary keys TableId rather than "Id".

CREATE TABLE User (
    UserId int IDENTITY(1,1) NOT NULL,
    Username VARCHAR(50),
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
)

Rather than...

CREATE TABLE User (
    Id int IDENTITY(1,1) NOT NULL,
    Username VARCHAR(50),
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
)

This avoids ambiguity in some SQL - and avoids joining primary key fields to foreign keys with a different name. Good for clarity.

However - and I think Ruby on Rails Active Record started this - ORMs such as Dapper Contrib require less configuration if the Id of a table / model is called "Id". To me this is an indication of best practice and is to be encouraged.

This is Dapper Contrib having an opinion and recommending a best practice.

Having said that very few example sites or code I have found (beyond dapper documentation) - using Dapper or not - use "Id" - most use "TableID".

Are there advantages beyond not having to add the [Key] attribute to the "TableID" property of a model to get it to map - and what is "best practice" when doing green field development?

Yes this question has been asked many times - but not with direct relation to ORMs - and not specifically about ASP.NET MVC 5 & Dapper Contrib. These are new(ish) factors.

Or am I just flogging a dead horse here - is it purely personal preference and not that important - even when ORMs are taken into account?

A question pertaining to primary key naming preferences.. Let the holy wars begin. — Alternatex, May 27 '16 at 13:15

score 1 · Answer 1 · edited Apr 12 '17 at 07:31

Does your DBMS care about which naming convention you use?

No

Do you care?

You probably do, just like any other human would. Everyone has a preference.

Do your colleagues care?

Yes? Consult with them.

Does your ORM care?

You should be able to answer this one yourself. I'm not familiar with Dapper but I have experience with Entity Framework, NHibernate and Eloquent and I find it ridiculous for any ORM to force a naming convention.

Does it make ever sense to name a primary key TableNameId instead of Id?

Not in my experience. If you do raw SQL queries and are worried about issues when doing joins you should use aliases. In fact, I find it more proper to name primary keys Id since that way I'm forced into the good practice of using aliases!

For me, User.UserId does not make sense. I always name my primary keys Id.

The only factors in this decision should be yours and your colleagues' preferences.

If there's something I missed out you can probably find it in this popular holy debate.

Dapper doesn't *force* a convention - it just knows (like Ruby on Rails with Active Record) that Id is a primary key - with other primary keys you obviously have to tell it explicitly. "Convention Over Configuration". "TableNameID" IMHO makes SQL more readable (many agree). Thanks for your input - you make good points. — niico, May 28 '16 at 00:55

Using "Id" as Primary Key in green field Dapper Contrib MVC 5 site

1 Answers1