How I Write SQL, Part 1: Naming Conventions (2014)

I personally prefer <i>person_id</i> to be the primary key name (instead of <i>id</i>) in both the person table and any table which has it as a foreign key. One reason is for join syntax:<p><pre><code> select * from person join team_member using (person_id) </code></pre> The other reason is <i>person_id</i> now unambiguously refers to the same field regardless if we&#x27;re looking at the PK or a FK. It&#x27;s always <i>person_id</i>.

Joe Celko, as well as ISO-11179, tell us to use collective names (&quot;personnel&quot;) or plural names (&quot;employees&quot;) for tables<p>As well, fewer keywords are plural, compared to singular, so there&#x27;s less chance of accidentally using a keyword if you use plurals<p>Haven&#x27;t yet seen an &quot;octopus&quot; table in production...

Also important is adapting to the existing naming conventions of the database, even if you don&#x27;t like it. (Unless the existing naming conventions cause more trouble that its worth like requiring quoted identifiers or redundant prefixes&#x2F;suffixes).

For naming stored procedures, there were a lot of helpful answers to my question &quot;What is your naming convention for stored procedures?&quot; on StackOverflow a few years ago. <a href="https:&#x2F;&#x2F;stackoverflow.com&#x2F;questions&#x2F;238267&#x2F;what-is-your-naming-convention-for-stored-procedures" rel="nofollow">https:&#x2F;&#x2F;stackoverflow.com&#x2F;questions&#x2F;238267&#x2F;what-is-your-nami...</a>

Without a rigorous attempt at justifying each of these rules, I don&#x27;t find this article particularly useful. For example, can someone link to or provide a formal explanation for why table names should be singular? I actually really wanted to read the full relational algebra rational for that one.

I know a reasonable amount of SQL but am by no means a database guru. Naturally, this makes me the local database expert at my current place of employment :&#x2F;<p>The other devs&#x27; eyes glaze over when I say things like &#x27;stored procedures&#x27; and &#x27;trigger functions.&#x27; Bah.

I don&#x27;t understand this one:<p>&gt; Mixed case identifier names means that every usage of the identifier will need to be quoted in double quotes<p>I&#x27;ve used quite a few RDBMS engines, including most mentioned by the author, and I&#x27;ve never had to quote mixed-case identifier names. They work just the same as all lower-cased names or as any other case-sensitive language.<p>Most of the programming languages I use typically have the convention of using PascalCase for classes and public fields&#x2F;properties so I prefer to use that convention for tables and columns (and then everything else for consistency). When doing operations between the application and the database, the name is exactly the same without the need for translation.<p>Otherwise, I think it&#x27;s a good list.

How come I have <i>never</i> in a multi decade career come across &quot;i18n&quot;.<p>I though the canonical way of doing this was to write KEYWORDS in caps and use camel case for Variables.<p>Also never really brought into adding the type as part of a name - your type is already defined in your schema.

Lot of these is debatable. For example, I have preferred FirstName or even “[First Name]” instead of first_name in sql because lot of tooling uses these names to generate UX. Similarly using Person.PersonID instead of Person.ID gives consistency in diagrams and foreign key naming. I have used both approaches with its own pro and cons.

You should follow the conventions that make life easier in the rest of your tooling.<p>The fact is, you&#x27;re probably going to be issuing more SQL via abstractions like ORMs or querying libraries than raw SQL. If you need to work against the grain of those libraries to map your model, what upside are you getting?<p>If most of your data is queried via ActiveRecord, for example, you should use plural table names.

&gt; Avoid reserved words<p>Glad you cleared this up for the rest of us.<p>FWIW, naming conventions are like opinions. Everyone has them, and they usually differ from person to person. The best naming convention is a consistent naming convention. Also, naming conventions differ greatly by environment. A group of SQL Server engineers are going to have different standards than those of people working on mysql.

No. That&#x27;s how you write sql within your organization. Also there are syntactic differences between SQL flavors ( postgres, mysql, mssql, oracle, etc ) that make a SQL standard unrealistic.<p>The only generic rule is &quot;be consistent&quot;. Whatever convention&#x2F;style you choose, it should be consistent.