>Make no mistake, encoding multiple values in a single string is generally a bad design. But it has nothing to do with first normal form. First normal form mean a column should not allow relations as values. A comma-separated string is still just a single string from the perspective of the database type system.
Is like say "Is wrong to store a large json in a cell in an array, that make it not an array".
A relation is A VALUE. That is.
The atomicity of the data INSIDE each "cell" is orthogonal to the fact the that relation is a relation.
This is the point.
And I agree, TFA is just flat out wrong.
A relation Manager with a CSV-valued column SubordinateIds or columns named "Subordinate1Id" through "Subordinate9Id" is most definitely not normalized because those are in effect a (bad) simulation of hierarchical-database-style repeating groups. That they have distinct names (or positions in the CSV list) is an artifact of the representation and not really part of the data model.
But the relational model CAN'T say it. The normal forms are properties of the way you can do the design, but can't go that Deep.
In other words: You can adhere 100% on SOLID OOP principles and your code is still conceptually wrong!
If pay attention to the article, his issue is that people latches on the apparent definition of 1st normal form, but misunderstanding it and probably coming to the wrong conclusion about how make the tables...
Also, presuming DB support for some format like JSON, you can encode a relation into a JSON value, stick it into a column and then, at query time, use a function like Postgres's json_to_recordset(json) to emit a relation (set of tuples). Postgres doesn't know how to deal with it "as is" (i.e. it forces you to treat the result of a function as a named joined table in your query, rather than treating it as an concrete value) but it does understand where it is in relation (heh) to the rest of the row, since you can unnest() values from this virtual table and get the right results out.
As awful as that sounds, we're actually doing that in production to normalize data coming from redis_fdw (which gives you, basically, (key text, value jsonb) rows for Redis hashes) into relational shape.
I think the reason people even ask questions like what does 1NF even mean is because they're not learning relational algebra, they are learning SQL. So if every table is in 1NF by default, it begs the question what is the point of 1NF? And the point is that the relational algebra is bigger than SQL, a realization that leads you to potentially interesting projects like CJ Date's efforts to build truly relational databases.
Your premise isn't fulfilled: Not every SQL table is in 1NF by default, it's quite easy to fuck that up. As illustrated by the whole "list of telephone numbers in a single column" point discussed here.
What we're using the database to do is to store information about the real world. 1NF is about eliminating nested relations. A list of telephone numbers is a relation -- a one-row table, if you will.
> Normalized relations, and the relationship between one normalized relation and another, mirror real-world concepts and their interrelationships. -- https://en.wikipedia.org/wiki/Database_normalization#Minimiz...
In the real world, a phone call is to a single phone number, not to a list of them.
> To satisfy First normal form, each column of a table must have a single value. Columns which contain sets of values or nested records are not allowed. -- https://en.wikipedia.org/wiki/Database_normalization#Satisfy...
A list of telephone numbers is a set of values, not a single value. Several strings "in one".
Absolutely agree, this is the definition of 1NF. So if you have a relation which has an attribute which has a domain which itself include relations - then you have a 1NF violation.
"From the perspective of the database" is perhaps too vague (although Codd uses almost the same phrasing), the question is if the value is atomic with regard to the relational operators (project, join etc.). Can you INSERT a tuple into a value? Then the value is a relation.
I get that at a certain abstraction level a comma separated string value could be considered a list or a relation. But then it would be at a different abstraction level than the one where the containing table is a relation. Because you can select rows and column from this outer relation using the relational operators, but you cant do the same for a relation encoded into a string value.
as an aside I think a lot of this sprouts from subtle ambiguity in the language. you really shouldn't just drop all articles and expect your english to still make sense!
The question is what "normalized" means. It does not mean "best practice data modelling" which is a much broader subject.
The article does not say delimited values are good design.
I would say he is confusing the physical representation with the logical model.