Using `type` as database column name

user_1006183 0
Viewed 9417

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
postgresql mysql sql
4 Answers

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.

user_23991180

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.

user_6427060

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.

user_9675770
Related