first time I model a database in quite some time and I see that Postgres has the ability to define custom enum types very similar to enums in programming language.

My question is when should types be used instead of a table with foreign key ?

In a column like "sex" it looks obvious a type is good choice, but should I do this for all "lookup" tables ? Nationality ? Employee Status etc ?

Thank you for your comments and thoughts!

CodePudding user response：

Well, it is generally a good idea to use custom enum types in situations where you have a fixed set of values that a column can take on. In your example, a column that represents a person's sex might have a fixed set of values such as "male", "female", and "other". In this case, using a custom enum type would be a good idea because it would prevent you from accidentally entering invalid values for the column.

On the other hand, if the set of possible values for a column is not fixed, or is likely to change over time, then it might be better to use a separate table with a foreign key constraint instead of a custom enum type. For example, if you have a column that represents a person's nationality, it might be better to use a separate table to store the possible nationalities, and use a foreign key constraint to ensure that only valid nationalities can be entered for a person. This approach would allow you to easily add, remove, or update the possible nationalities without having to change the schema of your database.