Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have always had the pk of the table as this pattern: lowercase tablename + Id.
That was what I was taught in college and didn't think about it. When I was challenged on it from someone using mongo db (a non-relational db) I couldn't figure out why I have ever done it this way?
The only thing I can think about is that some ORMs have it so that the auto build pk columns that normally match this pattern.
Any insight would be greatly appreciated.
There's no weighty reason to do one or the other. Both styles are common and work fine.
AFAIK: Pro: You always know how to join, as foreign keys use the same column name in each table, and join queries are easier to validate.
Con: You can't declare a supertype in your app that has the primary key as a property. IE it's a relatively common pattern in .NET to have
public abstract class Entity
{
public int Id { get; set; }
}
as a common base type for your entitites.
Some may argue that you shouldn't be adding synthetic keys to tables unless there's an actual need, but that's really a different issue.
