How I Write SQL, Part 1: Naming Conventions (2014) | Hacker News Reader

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

launchbylunch.com

164 pointssehrope78 comments

I personally prefer person_id to be the primary key name (instead of id) in both the person table and any table which has it as a foreign key. One reason is for join syntax:

    select * from person join team_member using (person_id)

The other reason is person_id now unambiguously refers to the same field regardless if we're looking at the PK or a FK. It's always person_id.

I use T-SQL, and this is actually why I always join with the table name:

SELECT * FROM Person JOIN TeamMember on PersonId = Person.Id

I'd bring it a little further and would write:

SELECT * FROM Person as P INNER JOIN TeamMember as TM on TM.PersonId = P.Id

I have:

- Aliased each table and prefixed every field names with their table alias in my join conditions.

- Explicited the JOIN type.

The above:

- Reduces mistakes due to ambiguities that tend to generate unwanted duplicates rows in SQL.

- Increases the likelihood of getting an error at parse time, instead of run-time or analysis-time, thanks to added scoping.

- Works in any schema, no matter what naming conventions are followed.

- Keeps working as the query becomes more complex with multiples table aliases or self-joins, and similar field names appearing in the set.

- Better expresses intent. Sure JOIN defaults to INNER JOIN, but writing "INNER JOIN" shows that you genuinely expect any row not matching your condition to be removed from the result set.

As a newbie, this makes more sense to me:

  SELECT * FROM person, team WHERE person.team_id=team.id

(no unnecessary aliasing, no weird JOIN phrasing)

I can understand not using aliasing in a simple query, but I'd recommend against using these non-ANSI joins (deprecated syntax). What you're writing can be interpreted as either:

SELECT * from person CROSS JOIN Team WHERE person.team_id=team.id

SELECT * from person INNER JOIN Team ON person.team_id=team.id

That they happen produce the same result in a query is practically just luck. Changing the join from INNER to LEFT OUTER is also much easier than managing (+)'s in the WHERE clause, once you're used to it.

This is good advice, IMO. You will have to use left/right joins at some point, and having the join type at the join location is useful, but having the join conditions at the join location is immensely useful.

It's harder to see in your example, likely because you tried to keep it similar to the example presented, but once there's more than a couple joins, having the join conditions close to the join is essential for keeping track of what's going on. e.g.

  SELECT * FROM person, team, role, group, person AS lead
  WHERE person.team_id=team.id
    AND person.role_id=role.id
    AND person.group_id=group.id
    AND group.lead_id=lead.id

Compared to:

  SELECT * from person
    INNER JOIN team ON person.team.id=team.id
    INNER JOIN role ON person.role_id=role.id
    INNER JOIN group ON person.group_id=group.id
    INNER JOIN person AS lead ON group.lead_id=lead.id

And let's be clear, we know these should be left joins, because the chance some person doesn't have a team, role, group, or a group ends up without a lead is high when sampled over time. And simulating left joins with the non-ANSI joins quickly gets unwieldy.

I'm not a fan of short aliases. They obscure what you are attempting to do.

Personally I write very little SQL anymore (the ORM does that for me unless I need performance), but the only time I use aliasing is when the same table is joined multiple times.

Like:

SELECT Mother.Name, Father.Name, Child.* FROM Person Child JOIN Person Mother ON Mother.Id = Child.MotherId JOIN Person Father ON Father.Id = Child.FatherId

That said, this naming convention also obscures what Person.FatherId points to without looking at the FKeys. So Take that with a grain of salt.

Aliasing a query that short just obfuscates intention. It might be necessary for some larger queries, or joining the same table multiple times, but there is a readability cost you are paying for it.

For example:

JOIN team_member ON team_member.person_id = person.id

It's completely self evident what is being joined without the need to trace back to the table aliases.

Pretty much all of your advice is premature optimization in my eyes. You can/should do those things when they are needed, but there is no reason to automatically write every single SQL query that explicitly.

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.

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.

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

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)

An interesting convention. I'm personally of the opinion that since I'm explicitly defining foreign key constraints and relationships in the database anyway, and that's easily viewed by querying the table definition, putting much more in the name itself is both redundant and causes confusion (or at least extra work and annoyance) when used outside the database.

I don't name the primary key field id because I'm denoting a type, I call it id because it holds the identifier for the current record. I don't call the foreign relation identifier person_id because it holds a "person" and is an id, I call it person_id because it holds the identifier for a person record, and that's a valid description for what the field holds. The constraint tells how the relation is defined.

> 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

So, do you call those fields that index works on col1_ix1 and cal2_ix1 or something? If not, why do you denote the primary key with PK, and not indexed columns? Both have explicit definitions in the schema that specify exactly how they are defined, one a primary key, the other an index over multiple columns.

To me this sounds a bit like how Hungarian Notation[1] had it's original purpose lost as it shifted over time to encompass a larger, less well defined set of behavior which didn't always deliver an actual benefits.[2] To me, if the system is enforcing the values, and you can introspect the system, that's good enough.

That said, I did spend a while reading an IDEF1X explanation and HOWTO last night, and there are some interesting points and arguments there, so I'm not entirely set in stone with these views.

1: https://en.wikipedia.org/wiki/Hungarian_notation#Systems_vs....

2: TL;DR "Apps Hungarian" prefixed variable names with info about what they were supposed to contain at a high level, such as rwFoo containing the row of Foo. Systems Hungarian came along later and they used the prefix to encode the type, such that iFoo might mean an integer Foo. The benefit of this in a language such as C or C++ that requires explicitly typing the variable anyways is debatable.

3: http://www.softwaregems.com.au/Documents/Documentary%20Examp...

I'm with you but in my experience it's a lost fight. Most of the projects I come across these days follow the id convention.

The advantages of using person_id are even more obvious in multiple joins, such as a star schema where you can using(person_id) all the things, reducing both the typing and the cognitive load.

I suspect that if this convention was more pervasive, programmers would be a little bit less afraid of diving into SQL.

Well, at least we're settling with some convention, so it's not all bad.

EDIT: typo

Joe Celko, as well as ISO-11179, tell us to use collective names ("personnel") or plural names ("employees") for tables

As well, fewer keywords are plural, compared to singular, so there's less chance of accidentally using a keyword if you use plurals

Haven't yet seen an "octopus" table in production...

I did a Google search for elaboration on this and apparently there is disagreement that ISO-11179 says this at all:

https://social.msdn.microsoft.com/Forums/vstudio/en-US/d5f2f...

> Yes, this is the same version as I found, but the closest thing I could find to addressing table names in the paper itself was an "Object Class name", something like an OOP Class or something you'd find in a UML diagram, but not really the same as a table name, and in any case all the examples were singular.

> Was actually kinda hoping Celko would deign to comment on this himself as he seems to be the chief proponent of the "collective identifiers as specified by ISO 11179" meme.

"To remind users that tables are sets of entities, ISO-11179 Standard likes to use collective or plural nouns that describe the set of those entities for the names of tables. Thus 'Employee' is a bad name because it is singular"

Page 10, SQL For Smarties (Celko), 5th Ed

If Celko says it's right, it's right

OK, but the ISO-11179 Standard doesn't seem to actually say that. Here's a purported copy of Part 5 -- Naming and identification principles -- of the standard (as linked to from the aforementioned MSDN forum thread:

https://www.ftb.ca.gov/aboutFTB/Projects/ITSP/Part_5_Naming_...

The word "collective" doesn't show up in the document.

The word "plural" shows up twice, both times in item #a of "Lexical rules":

> a) Nouns are used in singular form only. Verbs (if any) are in the present tense.

> NOTE In Japanese, this rule shall not be applied because of no plural form of nouns and no distinction of verb tense.

The only reference on Wikipedia to the ISO 11179 standard making that recommendation has since been deleted for being unsubstantiated:

https://en.wikipedia.org/w/index.php?title=Data_element_name...

edit: The apparently inaccurate content is talked about on the "Data element name" Talk page: https://en.wikipedia.org/wiki/Talk:Data_element_name

I looked for pluralization in the standard, and couldn't find it. Of course, I only glanced over the Table of Content.

This sounds like a brace style argument. The worst thing is to have a strong opinion.

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

I agree, consistency is worth a lot more than using a slightly better convention. It would also create surprises in the sense of "principle of least surprise" which is in general a good guideline while designing APIs schemas and so on.

Probably the worst thing I've encountered is a junior engineer trying to encourage me to change a coding style by having two coding styles coexist.

I just kept repeating, over and over, that I expected the coding-style to be consistent. It was totally over his head, and he totally didn't even bother looking to find a code formatting utility to do a One-Shot style change.

For naming stored procedures, there were a lot of helpful answers to my question "What is your naming convention for stored procedures?" on StackOverflow a few years ago. https://stackoverflow.com/questions/238267/what-is-your-nami...

Without a rigorous attempt at justifying each of these rules, I don'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've been through the relational algebra, I found this to be a nice, quick re-cover. More like a checklist than a full inspection.

I'm sure this SO answer will satisfy you: https://stackoverflow.com/a/4703155/34549

That answer satisfied me initially, but less and less so the more I read. It became very obvious by the end that what is being represented is one "standard", but presented as the only possible correct solution.

It starts off with Yes. Beware of the heathens. Plural in the table names are a sure sign of someone who has not read any of the standard materials and has no knowledge of database theory. I thought the author was being flippant, but it became increasingly obvious that this is a true reflection of their dogmatic view with regard to this topic.

Even if this is the same view I would settle on with all the knowledge, being presented with what is obviously a single perspective with no acknowledgement whatsoever of any positive aspects of alternatives causes me to instinctively distrust quite a bit of the reasoning presented.

performancedba answers ... the sound and the fury.

My own view is they should either be all plural or all singular. Just pick one. But I do gravitate to singular because all nouns have naturally consistent singular words. The same cannot be said of plural. e.g. Moose, Cactus, and any other word that ends in an 's'.

I used to do all plural but then I read a good argument online for doing singular so I switched to that for the next project and I've done singular ever since.

The few advantages of singular:

1. It's not always clear what the plural of something should be.

2. Chances are the singular maps better to your application layer (Person class <=> Person table).

That's a fair point, but keep in mind that the author isn't suggesting these are the "correct" ways to write SQL. They're just his ways.

I don't think one standard is necessarily better than another, but the important thing is to have rules. Over the years I've adopted similar rules for myself, and just internal consistency is so much better. I have old projects with table names including: "logs", "log_requests", "log_users", "game_logs", etc., not to mention mixing of plural and singular, to the point where I need to `show tables` before writing any query just to remember what I even called the table I need.

Relation definitions define types. Types have names. Tuple in a relation is an instantiation of the corresponding type. And we use singulars for class/types names in most programming systems (e.g. class Person vs class Persons).

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 :/

The other devs' eyes glaze over when I say things like 'stored procedures' and 'trigger functions.' Bah.

I don't understand this one:

> Mixed case identifier names means that every usage of the identifier will need to be quoted in double quotes

I've used quite a few RDBMS engines, including most mentioned by the author, and I'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.

Most of the programming languages I use typically have the convention of using PascalCase for classes and public fields/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.

Otherwise, I think it's a good list.

In Oracle this is kind of hidden away since it is case sensitive, but unquoted identifiers are silently converted to uppercase, quoted identifiers are used as-is. So, for queries against something like

  create table foo (
    Bar integer,
    "Foobar" integer
  );

Referring to Bar, BAR, bar, and "Foobar" will work, but foobar and Foobar will not.

If you are not quoting your mixed-case identifiers, then they are not mixed-case. They are being implicitly converted to either lowercase or uppercase depending on your database, and your capitalization is lost. It normally doesn't matter, until you are introspecting your database schema, at which point your code generation generates a Organizationrole protobuf message instead of an OrganizationRole, or your ORM fails to find OrganizationRole because PostgreSQL stored it as organizationrole.

This seems like a terrible feature of Postgres and Oracle (which apparently works the same but oppositely). All other database engines I've used have retained the case of unquoted identifiers.

I haven't used Postgres enough to notice this, it's almost a deal breaker.

I might be tempted to mandate that all identifiers be quoted than deal with half the possible characters for names. Although more likely all code-generation would happen on the application side with DB migrations so the database wouldn't the source of truth for identifier names, anyway.

How come I have never in a multi decade career come across "i18n".

I though the canonical way of doing this was to write KEYWORDS in caps and use camel case for Variables.

Also never really brought into adding the type as part of a name - your type is already defined in your schema.

Really? I have. In some circles (e.g., the IETF), i18n is an ancient acronym. People who've worked on operating systems (e.g., OS X, Solaris, RHEL, whatever) have to deal with L10N (localization). G11N (globalization) is I18N + L10N.

And then there's a11y: accessibility. This is all about making user interfaces accessible for people with low or no vision, low or no hearing, difficulty typing, and so on.

There are generally applicable laws requiring G11N and A11Y, and these fall heavily on OS vendors, which is why people who've worked on OSes tend to know these acronyms.

I18N -> dealing with Unicode in general, codeset conversions, font issues, ...

L10N -> dealing with translating system/application messages to the users' preferred languages (and how to even know they preferences) (think locales)

G11N -> I18N and L10N.

Localization is damned difficult. There's all sort of little bothersome things, like how to format numbers (which varies quite a lot) and dates (can't we all just use ISO-8601?!). And translating printf-like format strings is often non-trivial, especially when the coder doesn't stop to think about just how hard they might be to a translator as they write their code.

> G11N -> I18N and L10N.

That's a new one to me, but makes sense. I've been lucky enough to have heard of i19n and l10n for years (almost decades, and this point) but not had to deal with it much beyond tracking down a string in some open source webapp I was patching before deploying.

> can't we all just use ISO-8601?!

Preach on. I sometimes find myself filling out date fields in paper forms in YYYY-MM-DD without thinking. The elementary school my kids attend probably thinks I'm a weirdo. I know my wife does...

V10N --> Velociraptor

T15X --> Tyrannosaurus Rex

D11S --> Dilophosaurus

B11S --> Brachiosaurus

T9S --> Triceratops

S9S --> Stegosaurus

I think we have evidence that 12 characters is where should start the <first-letter><N><N><last-letter> shortening.

V10R you mean.

Of course

Also p13n -> personalization

You've worked on English only applications?

That could be it. Like him, I didn't run into that abbreviation for my first 20 years of coding. It wasn't until I needed to do a dual English/Czech project that it came up.

We're always learning.

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.

> For example, I have preferred FirstName or even “[First Name]” instead of first_name in sql

Do not do this in Postgres, it will be a pain in the ass since you will have to use quotes around everything.

Right. PG has appropriated square brackets for array notations, so you really just have to use double-quotes.

At least PG tries really hard to not add new reserved keywords, which means you mostly don't have to worry about your schema element names possibly conflicting with new keywords in future releases.

> Similarly using Person.PersonID instead of Person.ID gives consistency in diagrams and foreign key naming.

I assume you mean you just use PersonID as the foreign key. This oftentimes introduces ambiguity into what the relationship actually is. I prefer names that describe the actual relationship (e.g. author, owner, approver, etc) rather than letting other people guess what it is.

I think he's referring to using "[Table]ID" as the primary key on [Table]?

So now you have to join with the Person table on Person.PersonID from your local column PersonID. I much prefer the other way around: Table.ID with foreign keys being "TableID".

I find "[Table]ID" to be bit more readable in joins. I.e.

    SELECT * FROM table1 INNER JOIN table2 USING (table1_id);

    SELECT * FROM table1 INNER JOIN table2 ON (table2.table1_id = table1.id);

I usually want to reserve the noun like "author" as the embedded record after a join. That way "author_id" is always the key and then "author" is the json_agg joined object that embeds the whole record.

Otherwise you're actually introducing ambiguity imo.

if you need “[First Name]” so it shows up in an UI, you could always do first_name as 'First Name'. But I would say that's still general bad practice. In many cases, you shouldn't be exposing your column names through a UI, and most UI allow for alias

Person.PersonID stutters, you already know that you query on the person table you don't have to repeat it again. Naming it ID is the same consistency

I like "Person.PersonID" because then "alias.PersonID" will produce an error if "alias" does not have a "PersonID" column. If every table has an "ID" column then "alias.ID" pretty much never fails, even if you typed the wrong alias - you just end up joining on the wrong thing and getting the wrong result set.

> 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.

those tools are wrong (and I know roughly which ones those are).

> Similarly using Person.PersonID instead of Person.ID gives consistency in diagrams and foreign key naming.

it would be: person.id and the foreign key column that refers to it person_thing.person_id. This is much preferable to person.person_id and person_thing.person_person_id.

You should follow the conventions that make life easier in the rest of your tooling.

The fact is, you'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?

If most of your data is queried via ActiveRecord, for example, you should use plural table names.

Don't use an ORM is the answer just put in the effort to lean SQL

That is a weird middlebrow dismissal of a response with essentially no value to anybody.

Use an ORM when appropriate; when using one, follow its conventions. Don't use an ORM if it's not appropriate. This is much better advice.

I'm north of 40 now, so while I both love and agree with your advice, I'd like to add a caveat. Even if you are lucky enough to be able to use an ORM for your entire career, you will still be well served by learning and truly understanding SQL.

In that sense, I'd argue that an ORM is only appropriate if your understanding of SQL and database design are strong enough that you can understand what's happening under the hood if/when everything goes to shit!! :)

Solving problems with Postgres is wildly different than solving them with mssql informix or Oracle. So I would say "there is no SQL".

I don't agree with that. Once you understand the basics of building performant databases/queries, that knowledge will persist across databases. There are small differences in syntax and features, but any remotely competent person should be able to pick those up while they go, provided they understand what happens under the hood when they execute SQL.

In what way are they wildly different? I spent 15 years working in Oracle and the last 2 years working in Postgres. They are slightly different (Postgres adheres to the standard much more closely than Oracle), but 95% of my knowledge of SQL transferred from one to the other.

A simple example would be "how to select groupwise maximum" where Postgres would respond well to a correlated subquery and Oracle would respond well to a window function and derived table. The engines and optimizations are different.

I absolutely agree with that!

An ORM is an abstraction of a concrete system. For very simple use cases, that’s fine. For anything even slightly complex, the ORM becomes a tool that you can use to enhance code readability, or reliability, or modularity. But it’s essential to know what is going on underneath before using that abstraction, much like most other systems. There are too many times I’ve witnessed a less experienced developer build a shockingly expensive n+1 query using an ORM to doubt that :)

>Use an ORM when appropriate; when using one, follow its conventions. Don't use an ORM if it's not appropriate.

It's just as vague. If you know of a good article that goes over the details, that'd be cool :^)

This injunction doesn't really scale across a team.

I've spent weeks of my life tuning SQL, to the point of writing SQL generation libraries to effectively override the database optimizer when it consistently makes poor decisions in specific use cases. But I don't expect the rest of the team to know SQL as well as I do.

When I'm writing or generating SQL, I don't really care much what the naming convention is. If it's consistent, then SQL is easier to write. Consistency is more important than the specifics of any conventions.

I am not talking about the level of a Guru (sample quote "oh Dijkstra was my first boss") - but being able to handle the standard sorts of queries.

maybe we should have a fizbuzz for SQL as a filter :-(

And the standard sorts of queries are best handled via an ORM!

It is true that most of my SQL goes through ORMs. However my most complicated SQL is always constructed by hand. Furthermore note the point about how often applications get rewritten against the same database. You should not assume that future code will use the same ORM that you are using now.

It depends on the situation. If the queries you're writing deal are heavily integrated with application models and logic, then using the ORM is probably the way to go (especially with the ORM does some client side caching and other optimizations). Of course, if you start to see that the ORM's queries underperform compared to raw SQL then you should check to see what SQL the ORM is spitting out. I've seen SQLAlchemy create some very poor queries compared to what would be expected and ended up writing parts of those queries in raw SQL, but those cases are pretty uncommon when most of the logic for the application is simple gets/updates with filter/join conditions.

It does depend on the situation.

My experience has been that transactional code is usually best written with an ORM, and complex reporting code winds up better with SQL.

Hand-constructed SQL, though, can cope with any naming scheme. And I'd argue a consistent naming scheme, using tooling as a forcing function, is better than inconsistencies you'd get with a big team of people writing their own SQL.

> Avoid reserved words

Glad you cleared this up for the rest of us.

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'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.

The only generic rule is "be consistent". Whatever convention/style you choose, it should be consistent.

Reply on news.ycombinator.com