6 ms·

I would argue enums are less complex and easier to use. Imagine we were talking about a date field instead which is more complex? A products table with a colum

I would argue enums are less complex and easier to use. Imagine we were talking about a date field instead which is more complex? A products table with a column called date (of type date) Or a products table with a date_id column and a dates table with the columns date_id, date_year, date_month, date_day Sure the second option may seem more relational but it is more complex. It allows you to do more relational things later if you want to attach things to a date like an is_holiday or a was_raining column. So the question is, is there any value in adding the extra complexity now? If you foresee having to add more information to a status in a relational way like an is_active column then maybe a joined table is better.

dragonwriter 8y ago
> It allows you to do more relational things later if you want to attach things to a date like an is_holiday or a was_raining column. While I get that calendar tables are fun and all, if you have a date type you can just add a “holidays” table for that if you need it later, without a table of all dates.
- jaggederest 8y ago
  Precisely. This is exactly why decomposing things into relations is important. The naive approach is to "keep all the things and all their details in one big table", and that's what causes so many problems.
jaggederest 8y ago
An enum is a relation, it's just a relation that is not stored in the same kind of table that other data is. Your example is really a fairly poor strawman. I would suggest something more like a product with a "release date" column, which would be decomposed into a "releases" table with a foreign key. That's the kind of decomposition I've done a lot, and it's usually a useful one. Another example would be having an "address" field, versus having number, street, apartment/suite, city, state, zip, country. I generally go with address data in a separate, relational table, instead of inline (user_addresses vs on the user table directly), and I've found that to be very useful at times. In a lot of cases, you actually want a separate table (keyed on the real value, of course, not a generated foreign key) that stores things like zip codes or countries, because there is a lot of ancillary data to associate with them. If you value homoiconicity, you want all your data to be in as similar a format as possible. Enums are a special case, and so you should avoid them unless you have a strong reason otherwise.
- mamcx 8y ago
  > An enum is a relation This is TRUE. Most people ignore the relational model despite being so simple to learn. But one important thing to note, is that similar how you can say "anything is a object" you can say "anything is a relation". This is valid SQL: SELECT * FROM (SELECT 1) a And because "1" is a relation, a enum is. Normal rdbms fail at operate to inner values (like the array feature on postgresql) and don't allow to put tables into cells. This is valid at the relational level but you need to do hacky stuff for common rdbms. SELECT Head =(SELECT * FROM INVOICE) I'm working in a relational language, and this will be valid: for i in 1 do and select value:Some(1).... where value.isSome
  - krychu 8y ago
    Enums are not relations but data types as indicated in the first paragraph here: https://www.postgresql.org/docs/current/static/datatype-enum.html https://www.postgresql.org/docs/current/static/datatype-enum..., and by the related command ‘CREATE TYPE ...’. Relations are commonly used when referring to tables and views. What do you gain by calling types relations while introducing confusion and diminishing practicality of the relation concept?
    - mamcx 8y ago
      That is true. In postgresql. Not int the relational model.
    - dragonwriter 8y ago
      Finite data types are, or are isomorphic to, (constant) relations, they aren't relvars like tables/views (relations include both constant relations and relvars.) > What do you gain by calling types relations The fact that everything true of other relations is true of finite types.
- talaketu 8y ago
  I don't buy "enum is a relation" any more than "VARCHAR is a relation" or "REAL is a relation". These just data types that represent the domain or range of possible values that may participate in a relation.
ryanmarsh 8y ago
This thread is rehashing the trade offs of each form or “level” of normalization. Various normal forms serve various purposes. Use what makes the most sense for your read and write demands.
benjiweber 8y ago
One big caveat with enums in postgres is that adding values can't be done in a transaction https://www.postgresql.org/docs/9.4/static/sql-altertype.html https://www.postgresql.org/docs/9.4/static/sql-altertype.htm... It's modifying a type rather than values as it would be if we added a table, or schema if we added a column.
- patrec 8y ago
  It's annoying of course, but when is this a big problem in practice? I can imagine how it would be in theory, but in all cases where I needed to add an additional value to an enum so far, I could just do it independently of other schema changes and well in advance of deploying code that made use of the new value.
  - jaggederest 8y ago
    Why do that when you can define it as a foreign key into a proper relation and modify it with full transactional guarantees? I just don't see the advantage of bending over backwards. Remember, relations are "types" with existence checking built in and a whole powerful algebra around them. By dropping down to a more primitive type you gain very little and lose strong guarantees.
    - patrec 8y ago
      Convenience. Having a table mapping id to string is logically equivalent and plays nicer with transactions. But it's much less convenient. In principle SQL databases could have some sugar for foreign keys to tables that just bijectively map ids to human-readable identifiers, but they don't. I always wondered why not. With enums I get readable, type-checked values I can auto-complete on and that I can use "in-place". With a table I don't get auto-completion and I need to do a join for readability if my enums are "true" enums (and have an ordering).

dragonwriter 8y ago

> It allows you to do more relational things later if you want to attach things to a date like an is_holiday or a was_raining column. While I get that calendar tables are fun and all, if you have a date type you can just add a “holidays” table for that if you need it later, without a table of all dates.

jaggederest 8y ago
Precisely. This is exactly why decomposing things into relations is important. The naive approach is to "keep all the things and all their details in one big table", and that's what causes so many problems.

jaggederest 8y ago

An enum is a relation, it's just a relation that is not stored in the same kind of table that other data is. Your example is really a fairly poor strawman. I would suggest something more like a product with a "release date" column, which would be decomposed into a "releases" table with a foreign key. That's the kind of decomposition I've done a lot, and it's usually a useful one. Another example would be having an "address" field, versus having number, street, apartment/suite, city, state, zip, country. I generally go with address data in a separate, relational table, instead of inline (user_addresses vs on the user table directly), and I've found that to be very useful at times. In a lot of cases, you actually want a separate table (keyed on the real value, of course, not a generated foreign key) that stores things like zip codes or countries, because there is a lot of ancillary data to associate with them. If you value homoiconicity, you want all your data to be in as similar a format as possible. Enums are a special case, and so you should avoid them unless you have a strong reason otherwise.

mamcx 8y ago
> An enum is a relation This is TRUE. Most people ignore the relational model despite being so simple to learn. But one important thing to note, is that similar how you can say "anything is a object" you can say "anything is a relation". This is valid SQL: SELECT * FROM (SELECT 1) a And because "1" is a relation, a enum is. Normal rdbms fail at operate to inner values (like the array feature on postgresql) and don't allow to put tables into cells. This is valid at the relational level but you need to do hacky stuff for common rdbms. SELECT Head =(SELECT * FROM INVOICE) I'm working in a relational language, and this will be valid: for i in 1 do and select value:Some(1).... where value.isSome
- krychu 8y ago
  Enums are not relations but data types as indicated in the first paragraph here: https://www.postgresql.org/docs/current/static/datatype-enum.html https://www.postgresql.org/docs/current/static/datatype-enum..., and by the related command ‘CREATE TYPE ...’. Relations are commonly used when referring to tables and views. What do you gain by calling types relations while introducing confusion and diminishing practicality of the relation concept?
  - mamcx 8y ago
    That is true. In postgresql. Not int the relational model.
  - dragonwriter 8y ago
    Finite data types are, or are isomorphic to, (constant) relations, they aren't relvars like tables/views (relations include both constant relations and relvars.) > What do you gain by calling types relations The fact that everything true of other relations is true of finite types.
talaketu 8y ago
I don't buy "enum is a relation" any more than "VARCHAR is a relation" or "REAL is a relation". These just data types that represent the domain or range of possible values that may participate in a relation.

ryanmarsh 8y ago

This thread is rehashing the trade offs of each form or “level” of normalization. Various normal forms serve various purposes. Use what makes the most sense for your read and write demands.

benjiweber 8y ago

One big caveat with enums in postgres is that adding values can't be done in a transaction https://www.postgresql.org/docs/9.4/static/sql-altertype.html https://www.postgresql.org/docs/9.4/static/sql-altertype.htm... It's modifying a type rather than values as it would be if we added a table, or schema if we added a column.

patrec 8y ago
It's annoying of course, but when is this a big problem in practice? I can imagine how it would be in theory, but in all cases where I needed to add an additional value to an enum so far, I could just do it independently of other schema changes and well in advance of deploying code that made use of the new value.
- jaggederest 8y ago
  Why do that when you can define it as a foreign key into a proper relation and modify it with full transactional guarantees? I just don't see the advantage of bending over backwards. Remember, relations are "types" with existence checking built in and a whole powerful algebra around them. By dropping down to a more primitive type you gain very little and lose strong guarantees.
  - patrec 8y ago
    Convenience. Having a table mapping id to string is logically equivalent and plays nicer with transactions. But it's much less convenient. In principle SQL databases could have some sugar for foreign keys to tables that just bijectively map ids to human-readable identifiers, but they don't. I always wondered why not. With enums I get readable, type-checked values I can auto-complete on and that I can use "in-place". With a table I don't get auto-completion and I need to do a join for readability if my enums are "true" enums (and have an ordering).