13

One common issue I run into when naming columns in a new database table is the right name to use for classifying subtypes. The most natural column name is typically type, but I try to avoid using SQL keywords or reserved words in my naming.

I'm aware that type is a non-reserved keyword in both MySQL and Postgres, so I can use it, but should I?

What is current best practice around using type as a column name? Is there a synonym which is so broadly equivalent that it just makes sense to use that?

Over the years I've spent way to much time trying to pick other names and this has come up twice in discussions in the past week, so I wanted to see if there is any clear consensus around this?

In case it helps anyone else, some alternatives I've used in the past to try to get around this include:

  • category
  • kind
  • subtype
  • type_of
  • role
  • class
  • <entity>_type
Matt Sanders
  • 8,023
  • 3
  • 37
  • 49
  • 1
    You should use the easiest one to understand. If `type` will make any other programmer understand what it is referring to, you can use it without problems. – Phiter Mar 22 '16 at 16:47
  • 1
    I have a strong tendency to avoid keywords alltogether, even if they are non-reserved. In most cases, there are better names ( datatype, registration_date). If I cannot find a better name, I just prefix them with Z: `ztype` , `zdate`, preferrably pronounced with a German accent ... (Note: there is only one (non-reserved) keyword that starts with a Z : `ZONE` : http://www.postgresql.org/docs/9.5/interactive/sql-keywords-appendix.html – joop Mar 22 '16 at 16:56
  • 1
    hibernate uses "dtype" for "discriminator type" if that helps – Neil McGuigan Mar 22 '16 at 19:22
  • Similar: [*Cannot create a database table named 'user' in PostgreSQL*](https://stackoverflow.com/q/22256124/642706) – Basil Bourque Sep 01 '22 at 21:10

4 Answers4

5

my suggestion is usually to avoid using keywords but not because they're reserved but because they're often actually ambiguous in and of themselves.

For example, say you have a customer table - with a column 'type' this might be filled with segmentation (high value/low value etc) or it could be filled with source (direct marketing/walk in etc).

"Type" is a low-value word. I'd usually recommend being as explicit as possible at the expense of column-length. For example 'CustomerSourceType' or 'InventoryLocationType' or whatever is actually clearer in the scenario.

Mark Taylor
  • 1,128
  • 8
  • 15
  • 1
    "Type" is a low-value word. Great point! That sold me on trying something else. – user1847 Feb 10 '20 at 21:43
  • my alternative was `category` but this caused me to use something more explicit. thanks – Kermit Oct 01 '20 at 20:37
  • This is similar to naming a column `date` or `timestamp` - it essentially documents nothing –  Nov 10 '20 at 06:59
3

Trailing underscore

The SQL standard explicitly promises to never use a tailing underscore on any keyword, reserved word, etc.

So if you name your identifiers (table names, column names, index names, etc.) with a trailing underscore, you need never worry about a collision.

CREATE TABLE product_ (
    name_ VARCHAR ,
    type_ VARCHAR ,
    …
);

The advice others gave to “avoid using reserved words” is naïve, is likely to fail, and is not portable. Many years ago I did a quick survey of various database products and their keywords. I collected a distinct list of about a thousand. Really. You would not believe how many non-obvious words are used in various ways by various products. Of course, collisions are context-dependent, so not all reserved words will be a problem if used by you in particular ways. But the trailing underscore trick relieves you of the need to study each product’s list of reserved words and monitor your codebase for potential collisions.

Naming is tricky. Of course you should make an effort to use the most clearly descriptive name possible. Sometimes, especially in a particular problem domain, a word like “type” might be appropriate. If so, add the trailing underscore (type_) and sleep well.

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
  • This is a creative solution and one I had never considered. It seems like this naming scheme might sometimes wreak havoc depending on your ORM but it is a good solution to the avoiding reserved words issue. – Matt Sanders Sep 08 '21 at 00:08
1

First rule,

Never ever ever ever use Keywords or Reserver words. The person coming after you will suffer.

If you are only using 'Type' for this time. You could use Types

WiredTheories
  • 231
  • 7
  • 18
  • 7
    Thanks for the suggestion! I generally prefer to keep my column names singular unless they are truly representing multiple things. – Matt Sanders Mar 23 '16 at 21:28
  • I'm here because the person before me use "type" as a column name. I can relate. People, please don't use type. I'm figuring out how to query this in Medoo as of the moment. Ugh. – Woppi Dec 10 '20 at 02:58
0

Naming a column "type" is technically fine it seems. I name my columns to avoid confusion with methods / keywords in the language / frameworks I might use to query to database.

Also, what if I add a new language or framework that interfaces with the DB.

"type" is a method name or keyword in many languages.

Perhaps for no good reason (it does take away some awesome column names). Shouldn't be that big a deal either way. Just a personal preference.

slindsey3000
  • 4,053
  • 5
  • 36
  • 56