[jklowden]

Perhaps TFA could explain what happened to math between 1977 and 2024. Because the meaning of "atomic" didn’t change, nor did the meaning of 1NF. The author pretends to explain 4NF, but never actually begins. He calls himself an "historian", which he might be. But he betrays very little knowledge of the relational model.

Repeating fields, which he proclaims as some kind of innovation, violate 1NF. They’re not atomic. Maybe the easiest way to understand it is that the list of languages can’t be used in SQL. If one teacher speaks Italian and French, SQL won’t find WHERE LANGUAGE = ‘FRENCH’. Nor will SQL join two teachers on that column.

SQL doesn’t recognize repeating fields in a column because it already has a way to represent them: as rows. That a teacher speaks two languages is not one fact, but two; not surprisingly, those two facts appear in two rows. ‘Twas true in in 1977. ‘Tis true today. Thus ‘twill ever be.

[layer8]

I mostly agree with you, however one could argue that character strings aren’t atomic as well. They are sequences (“arrays”, if you will) of characters, and query operators like LIKE demonstrate that strings aren’t just used as opaque values. You could in principle normalize character strings into a String table with columns “string ID”, “character position”, and “character value”. Somewhat similarly, types like DATETIME and INTERVAL can be regarded as non-atomic.

Whether a database column has a composite type is a trade-off regarding performance, storage requirements, and needing special operators for queries to work on the substructure of a composite value.

[petalmind]

> Because the meaning of "atomic" didn’t change, nor did the meaning of 1NF.

So what is the meaning of "atomic"? Are array-typed values atomic? (I think you can treat it that way, a lot of lists are immutable in different programming languages.)

> But he betrays very little knowledge of the relational model.

This is absolutely possible. Let me ask a couple of questions to borrow from your understanding of relational model.

> Repeating fields, which he proclaims as some kind of innovation, violate 1NF. They’re not atomic.

Sorry, what is "repeating fields"? I don't remember ever using this, how does it look like? Are you talking about array-typed columns? If yes, then why are they not atomic?

Also, I don't think I proclaim absolutely anything is "kind of innovation"? Postgresql supports array-typed columns since 2001, and it was planned in 1986 already as part of other "post-relational" features.

Two schemas "(instructor_id, List<skill_id>); PK(instructor_id)", and "(instructor_id, skill_id); PK(instructor_id, skill_id)" are isomorphic, thus I may be wrong but I don't see any anomalies here.

> Maybe the easiest way to understand it is that the list of languages can’t be used in SQL. If one teacher speaks Italian and French, SQL won’t find WHERE LANGUAGE = ‘FRENCH’.

Ah, so you seem to be talking about array-typed columns. I mean, you can find it easily by using "WHERE languages @> ARRAY['FRENCH']". The difference is mostly syntactical.

If you think that you are not allowed to access single elements of "languages" array because it violates "atomicity" of the value, then we should also say that you're not allowed to use queries like "WHERE name LIKE 'A%'", because they violate the atomicity of a string value. Don't you agree?

> SQL doesn’t recognize repeating fields in a column

Array-typed columns appeared in ANSI SQL:1999, a quarter of century ago. I'm not sure what you're talking about.

[arh68]

Atomic means nondecomposable [1]. I dare say array-typed values are not atomic.

Re: "(instructor_id, List<skill_id>); PK(instructor_id)”, are your Lists sorted ? Are your Lists actually Sets?: what prevents `insert values (100, [2, 2, 1])` ?

If you think those are isomorphic, how about “(json_data); PK(json_data)” ? With, you know, all the same data in the JSON. Tomato, tomato ?

I’ll just link this [2] PDF that seems to describe the present

> Does this mean that SQL:1999 allows databases that do not satisfy first normal form? Indeed, it does, in the sense that it allows “repeating groups”, which first normal form prohibits. (However, some have argued that SQL:1999’s ARRAY type merely allows storage of information that can be decomposed, much as the SUBSTRING function can decompose character strings—and therefore doesn’t truly violate the spirit of first normal form.)

[1] at least, according to Codd

[2] https://www.cl.cam.ac.uk/teaching/0304/Databases/sql1999.pdf