0

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?

  • 2
    A question pertaining to primary key naming preferences.. Let the holy wars begin. – Alternatex May 27 '16 at 13:15
1

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.

| improve this answer | |
  • 1
    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 0:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.