DEV Community

Discussion on: Database Naming Standards

Collapse
ovid profile image
Ovid Author

When I first published this article a long time ago, people complained that they needed to see the second version of the id query because they didn't see what was wrong with the first version. After all, origin.id = thread.id looks fine.

In the second version, because we use explicit names for the ids, you can immediately see that origin.star_id = thread.email_id is wrong. That's the point of this: if your naming conventions let you immediately see that a mistake is made, that's a huge win.

Collapse
superdre profile image
SuperDre

Your example is already wrong, wrong wrong. origin.id = thread.id you should immediately know there is something wrong as it both points to primary keys in different tables, so you do know something is wrong already.
And with origin.star_id = thread.email_id, yeah you immediately see something is wrong, but it's wrong due to 2 things (at least if we're going from the notion at least one of the two is the primary key), so it should be origin.star_id = thread.star_id or origin.email_id = thread.email_id or origin.origin_id = thread.origin_id or origin.id = thread.origin_id (and so on).

Collapse
detzam profile image
webstuff

Sorry about this but, from this i see that the problem us more of ambiguous aliases than column names.
But as i saud is a matter of opinion and project.

Collapse
aarone4 profile image
Aaron Reese

I've been a SQL developer for 30 years. And this is SO wrong.... Table.id is the PK (or at least the unique constraint) on this table. Table.other_id is the Foreign key to the related table. Origin.id = email.id immediately stands out as being incorrect because at least one side should reference the FK field unless one of them is a straight 1:1 extension table.

Table names should be singular as each row represents a single occurrence of the modelled entity, for the same reason your class names are singular. If you are going to conflict with reserved words, pick better table names: AppUser, BillingConstraint, ShippingTable.
I personally don't like underscored table names it makes code scanning harder because fields do tend to be underscored as are system configurations.
Right at the top you said SQL databases are case insensitive. This depends on the collation settings for the instance, database and table. Case sensitive collations are a nightmare!

Thread Thread
ovid profile image
Ovid Author

While I've laid out my reasoning and you should feel free to disagree. I point out my id naming reasons, you don't like them, that's fine.

As for table names being plural, as mentioned, many experts disagree strongly on this. Joe Celko, world-famous database expert and one of the authors of the SQL-89 and SQL-92 standards, argues in SQL for Smarties, that table names should be plural because they represent collections, not individual entities.

As for case-insensitivity, section 5.1 of the SQL-92 standard states (where the angle-bracketed words refer to a previously defined grammar):

There is a one-to-one correspondence between the symbols contained in <simple Latin upper case letter> and the symbols contained in <simple Latin lower case letter> such that, for all i, the symbol defined as the i-th alternative for <simple Latin upper case letter> corresponds to the symbol defined as the i-th alternative for <simple Latin lower case letter>.

SQL the language (not the data it manipulates) has been case-insensitive for decades.

Thread Thread
aarone4 profile image
Aaron Reese

I'll grant that there is no one perfect answer but I felt it was important to put across a different set of arguments. The most important thing is to be consistent with whatever naming convention you decide to use. The ANSI standard SQL-92 - The clue is in the title. That standard will be 30 years old next year, the world has moved on. Each vendor has proprietary extensions to the standard, my preferred flavour is MSSQL which allows you to define the collation rules for your database which includes whether the database and the data it contains is case sensitive, whether it uses the standard ASCIII character set or allows for 'foreign' characters such as Cyrillic, turkish or accented characters, whether Upper and lower case characters should be treated differently and whether NULL appears at the start or end of the index. This may not be standard, but it is 'real world'.
Please don't quote 'World Renowned' Joe Celko: I have had enough run-ins with him on SQLLServerCentral over the years to know that he is an absolutist and simply cannot accept that data is impure and that applications and their databases are not always (usually) developed by people with Doctorates in data management and who have the full picture of what the end solution will need to look like in 10 years time when the business needs are not well defined and you don't have an infinite amount of time. Joe has some good points and his books are comprehensive but on a personality basis he is way of the mark with understanding how to help people sove the problems they find themselves in. His attitude is - well you shouldn't have gotten there in the first place...