Primary Key/Foreign Key Naming Convention

Primary key/foreign Key naming convention

It doesn't really matter. I've never run into a system where there is a real difference between choice 1 and choice 2.

Jeff Atwood had a great article a while back on this topic. Basically people debate and argue the most furiously those topics which they cannot be proven wrong on. Or from a different angle, those topics which can only be won through filibuster style endurance based last-man-standing arguments.

Pick one and tell them to focus on issues that actually impact your code.

EDIT: If you want to have fun, have them specify at length why their method is superior for recursive table references.

Primary Key / Foreign Key naming convention in MVC

This is destined to be closed as opinionated, but I'd go with the first convention. It's more of a pet peeve of mine than anything, but it bugs me to no end when I see stuff like:

public class Student
{
    public int StudentId { get; set; }
    public string StudentName { get; set; }
    ...
}

Well, of course, it's the student's id and the student's name. That's the name of the class. Then, your API looks like student.StudentName, which just reads ridiculously.

Long and short, if the property is intrinsic to the class, do not prefix it with the class name. You should have an API like student.Id and student.Name.

When it comes to foreign keys, you should prefix it with the class name because that tells you about the relationship. Something like student.CountryId very clearly indicates that this is the foreign key for a student's country. In programming, readability and self-documenting APIs are paramount.

Foreign Key naming scheme

The standard convention in SQL Server is:

FK_ForeignKeyTable_PrimaryKeyTable

So, for example, the key between notes and tasks would be:

FK_note_task

And the key between tasks and users would be:

FK_task_user

This gives you an 'at a glance' view of which tables are involved in the key, so it makes it easy to see which tables a particular one (the first one named) depends on (the second one named). In this scenario the complete set of keys would be:

FK_task_user
FK_note_task
FK_note_user

So you can see that tasks depend on users, and notes depend on both tasks and users.

What is the naming convention for Primary Key column?

Columns should be named based on the data elements that they represent, not based on what constraints apply to them. A primary key column should be named in the same way you name any other column. The ISO 11179 standard has some useful guidelines for naming data elements.

Naming primary keys id vs something_id in SQL

Whatever you do, pick one or the other and stick to that standard. There are pros and cons for each.

I prefer SomethingID but other people prefer just ID. In the system I work with there are well over a thousand tables and having the PK and the FK have the exact same names makes things easier.

CakePHP Database Naming Convention

As far as I know the naming convention for the primary key is just id. So your event_dates ID shout just be id instead of event_date_id.

To your second part:
You named your ID event_date_id but looking for eventdate_id in your where-clause. So you forgot a _ there. If you change your primary key to id(see above) then don't to forget to fix your statement.

---

Related Topics