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?
Best Answer
Does your DBMS care about which naming convention you use?
Do you care?
Do your colleagues care?
Does your ORM care?
Does it make ever sense to name a primary key
TableNameId
instead ofId
?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 keysId
.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.