What is the best type for status in sql table?

user_14074056 0
Viewed 303

I need to store status of an entity, but i'm stuck with question of what is the better sql type. I'm using postgresql and see following possibilities:

  1. Varchar
  2. Enum
  3. Separate reference table

Enum is not bad even if i will add new statuses and now it seems to be more simple and clear then reference table. Is it right?

And another question is what is better option for performance in select queries? Enum is similar to int, but reference table turn to query with join or where exists query to filter by status.

Help me to understand what is better?

status enums postgresql sql types
1 Answers

If the status might be in more than one table, then I would recommend a reference table. This ensures that the statuses are consistent.

Within a single table, you could use a varchar() column with a check constraint. However, I would probably still lean toward a reference table.

A reference table has another advantage beyond consistency. It makes it easier to store information about the status -- for instance, short names, long descriptions, "default" values for certain attributes, and so on.

user_11440350
Related