SQL Schema Naming Conventions

Several weeks ago I asked on Twitter for SQL schema naming conventions from DBA professionals. (I’m always interested in the generally-accepted practices of related professions; when I can, I try to make my work as compatible with theirs as possible.)

I got back only a handful of responses, representing MySQL, PostgreSQL, and DB2 administrators, really not enough for a statistically useful sample. Even so, I’m going to present their anonymized responses here, because they led me to work I had not previously considered at length.

My questions were:

  1. For table names, do you prefer plural (posts), singular (post), or something else?

  2. For primary key column names, do you prefer plural (posts_id), singular (post_id), just plain id, or something else?

  3. How do you name many-to-many association tables? For example, if many posts relate to many tags, do you prefer combining the table names in plural or singular? If so, do you separate them with an underscore? The examples would be posts_tags for plural, and post_tag for singular. Or do you prefer another approach?

The answers follow.

Table Names

  • “Table and columns are singular, so create table item, account and not items, accounts.”

  • “Keep names singular. The reason behind that is that it was easy to reference column name with table name. Example: "user".first_name. The biggest challenge going with singular name is that most of the popular table names are considered keywords for the databases. Some of the examples: user, order, name, type etc.”

  • “Table names should be plural. That’s how I learned it, and it seems to make sense that a name for a collection of rows should be plural.”

  • “I prefer plural table names.”

  • “Plural – because it is a set of things.”

Primary Key Names

  • “Every table must have an id primary key (surrogate) using a sequence (identity is ok sometimes).”

  • “I prefer singular names for column without any prefix or suffix.”

  • “I would have said post_id but for the past several years I’ve switched to just id.”

  • “I prefer primary key always id.”

  • “Singular. For example, UserID.”

Association Table Names

  • “If I follow singular table names, I use post_tag_mapping. I like to use _mapping suffix to explicitly identify such tables.”

  • “We use plural_plural.”

  • “I prefer mapping tables singular.”

  • “I combine them as SingularPlural and generally have the dominant entity first as it owns things in the second entity. Ex: PostTags or UserRoles or StudentTests.”

What Does This Tell Us?

Not a whole lot, it seems. We might say “there’s no generally accepted practice” but with only 5 respondents that’s not a reliable conclusion.

Havig said that, one respondent summed up what seemed to be a common sentiment this way: “Most people will probably agree it’s about agreeing on a standard, and then being consistent with it.” I think that’s often the case with standards.

Another respondent noted, “Once upon a time you had production DBAs, and development ones that could do data modelling. These days it’s just production DBAs, and we always inherit designs as we come in later.” That certainly squares with my own experience. DBA professionals are generally hired much later as the business matures, and they’re stuck with whatever non-DBA-professional decisions were made before their arrived. The pre-existing schemas bind their hands.

What Would Joe Celko Do (WWJCD) ?

However, more than one respondent referred to Joe Celko‘s SQL Programming Style, which I immediately ordered and read through.

I thought Celko’s recommendations made a lot of sense. At first I thought I would have to copy the relevant sections here, but it turns out that Simon Holywell has already done so at his SQL Style Guide.

Celko’s answers to the above questions appear to be:

  1. For tables: “Use a collective name or, less ideally, a plural form. For example (in order of preference) staff and employees.” This one was especially interesting to me. The idea of using a collective name, not merely a plural name, makes a lot of sense to me, though it does not lend itself to automation.

  2. For primary key names: “Where possible avoid simply using id as the primary identifier for the table.” I gather from other reading that the recommendation is to use a natural identifier as a prefix; in the case of a posts table, that would be post_id.

  3. For association tables: “Avoid, where possible, concatenating two table names together to create the name of a relationship table. Rather than cars_mechanics prefer services.” On seeing it this way, it also makes sense to me, and I do not recall seeing it stated that way before.

Further, Celko lays out a series of uniform suffixes for column names. That by itself is pretty interesting.

Conclusion

If you’re starting a project from scratch, and are interested in following the advice of at least one SQL and DBA professional giant, you may wish to review the recommendations at http://www.sqlstyle.guide and try them out. Even better, buy Celko’s book. At the very least, by reading those recommendations, you’ll have gained a greater range of options to choose from.

UPDATE: If it was not clear from the introduction, this exercise was about discovering generally-accepted practices of DBA/SQL professionals (i.e., people whose primary job is to administer a database and write SQL schemas), not the preferences of application developers who happen to use SQL databases.

Are you stuck with a legacy PHP application? Subscribe to "Modernizing Legacy Applications in PHP" for tips, tools, and techniques that can help you improve your codebase and your work life!

Share This!Share on Google+Share on FacebookTweet about this on TwitterShare on RedditShare on LinkedIn

20 thoughts on “SQL Schema Naming Conventions

  1. I prefer using singular for table names. Because, a table is not a collection but a placeholder for it’s values. It makes more sense to use “employee.first_name” than “employees.first_name” in the code. Also, I feel it’s more intuitive to use “id” for primary key than “employee_id”, and use “employee_id” as foriegn key in other tables. When using “employee_id” as the primary key we will have to use “employee.employee_id” where ever needed, which is kind of awkward.

    I like the idea of using collective names like “staff” over “employees”; haven’t thought about that before. Need to do a research for more words like this šŸ™‚
    Thanks for the great write-up.

    • Hi,

      a table holds a collection of entities. Using plural for table names, thus, is natural, I’d say.

      This does not mean that you need to use oddities like “employees.first_name”: SQL implements table aliases (some refer to them as row variables). These row variables are successively bound to the individual entities of the associated table. Using singular for these aliases thus seems sensible:

      SELECT employee.first_name
      FROM employees AS employee

      Just my 2 cents.

      Best wishes,
      ā€”Torsten

  2. I preffer to use plural names for tables, with “tb_” as a prefix.
    Also for primary keys I preffer to use the singularized table name, wit “_id”.

    P.S.: I am not a DBA; just a developer.

  3. What about a public survey (GoogleForms/SurveyMonkey) to get some more samples? Then you can add a question for role and filter for DBAs, Developers, Hobbyists…

  4. If I recall from my college days, the academic way I was taught was tables and columns were to be singular. Associations I don’t recall but I think it was a combination of the two table names.

    The issue with singular names though is you can have conflicts with SQL verbs. Like if you have an “order” table instead of “orders” you’ll have to make sure to wrap the name in quotes every place you put it (not sure if ORMs, PDO, things like that do this for you) – but at least for manual SQL statements, and even things like phpMyAdmin (depending on version) you can run into weird issues.

    I used to try to adopt the singular method but due to that stuff, nowadays I seem to prefer plural or collective. I always try to be as specific with column names too (“post_id” vs. “id”) as I think it lends itself to better understanding and less confusion.

  5. I prefer singular for table names because it is clear that a table contains a collection of data so no need to use a plural. It also makes things easier in frameworks where you use namespaces, models, controllers etc. Like …

    table=customer
    namespace=Customer
    model=CostomerModel
    controller=CustomerController

    When you use plural for table names then it all gets mixed up in a confusing way.

    Also, I prefer car_mechanic over services because I can see at a glance from the outside what the table is about. It is NOT about defining a new entity service but about a relationship between car and mechanic.

  6. I would not recommend using any prefix for tables, maybe a suffix for some special types like indexes (_idx) and foreign keys (_fk) to prevent conflicts.

    But everything else is something that exists naturally in a database, a user(s) table holds a collection or user entities, get_user (user defined function) will return a single user entity, email_value is an e-mail value DOMAIN which holds an e-mail value.

    In ORM it’s more common to use a singular name for the class as the object only holds a single entity, but if you have collection object that holds multiple objects (like a Contacts object that holds zero or more Contact objects) it logical to use a plural class name.

    I actually used plural table-names for many years, but a DBA in a blogpost recommended using only singular. https://launchbylunch.com/posts/2014/Feb/16/sql-naming-conventions/ :

    Singular Relations

    Tables, views, and other relations that hold data should have singular names, not plural. This means our tables and views would be named team, not teams.

    Rather than going into the relational algebra explanation of why this is correct I’ll give a few practical reasons.

    They’re Consistent. It’s possible to have a relation that holds a single row. Is it still plural?

    They’re unambiguous. Using only singular names means you don’t need to determine how to pluralize nouns.

    Ex: Does a “Person” object go into a “Persons” relation or a “People” one? How about an “Octopus” object? Octopuses? Octopi? Octopodes?

Leave a Reply

Your email address will not be published. Required fields are marked *