Using `type` as database column name

Question

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
• group
• type_of
• role
• class
• <entity>_type

Answer 1

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.

Answer 2

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