undefined | Better HN

0 pointsdspillett7y ago0 comments

I prefer to keep ID column names descriptive even if it does lead to repetition like Person.PersonID. That way columns that identify a person always carry the same name and you are never left guessing what a more anonymous "ID" refers to or fall into one of a couple of traps where the parser disambiguate one in a way you were not expecting (though this is also caught by consistently using two+ part names when referring to columns, which I also prefer to do). It is particularly useful if the same entity is joined into a query multiple times with different aliases.

There are cons, of course. This is a matter that divides people and when working with other people's projects you have to ignore your own preference and follow the "local" convention.

0 comments

kbenson7y ago

Although the alternative allows for a bit of metadata to be encoded in the name. For example, if "id" is always the local item descriptor, you can almost always assume anything ending in "ID" or "_id" is a foreign key, and either the table name or relation is encoded in the prior part of the name.

It's a small thing. You'll likely know enough about the tables to be able to know this information anyways. Then again, knowing which "id" field you want is pretty obvious too. In the end, there are pros and cons to both, and it's mostly preference.

That said, once it leaves the database, I much prefer my records have short id fields, which likely influences by schema design to some degree.

always_good7y ago

Agreed. For me, "id" means the PK (or at least just some surrogate key), and "*_id" is an FK.

dspillettOP7y ago

If I'm going to use in-name property marking[1] then I'll use PK for the PK, so instead of Person.idPerson that would be person.pkPerson (and perhaps fkPerson instead of idPerson in child tables).

I used to just use ID as the name for surrogate primary keys, but find being more explicit to be helpful for clarity. It is one of a number of habits I used to have in the name of being concise that I now prefer not to do these days in the name of being descriptive.

[1] which I do for keys themselves and other table supporting objects[2], the PK for a table called Thing is explicitly named pkThing rather than letting SQL Server pick a name [2] for instance an index on Thing covering col1 and col2 is ix_Thing_col1_col2 (unless the index exists for a fairly specific reason that is somehow unclear from what it covers/includes in which case that reason is in the index's name)

1 more reply

j / k navigate · click thread line to collapse