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.
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.
Various normal forms serve various purposes. Use what makes the most sense for your read and write demands.
It's modifying a type rather than values as it would be if we added a table, or schema if we added a column.
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.
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).
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.
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
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)
for i in 1 do
select value:Some(1).... where value.isSomeRelations 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?
> What do you gain by calling types relations
The fact that everything true of other relations is true of finite types.
Not int the relational model.
Like a foreign key constraint?
Are they ordered in a way that's different from an integer value that's a foreign key into another table?
The main difference I can perceive is that the ordering is non-obvious to a human, unlike integers, which seems less useful, not more.