On the ordering of rows and 1NF...

Is the disconnect here not between the idea of rows which are ordered when used, and rows which are inherently ordered? If I have a table:

create table addresses(

address_line_index tinyint not null

check (address_line_index between 1 and 3),

...

)

Then the address_line_index represents an attribute by which I may choose to meaningfully order the rows when selecting from the table, but does not represent an inherent ordering to the rows in the table. The conceptual relation for this table itself is thus "unordered".

Similarly, would it violate normal form to have a log table, of the form:

create table event_log (

event_datetime datetime not null,

...

)

Where events are inserted into the log in date ascending order (because this is the order of events as they occur)? I don't think anyone would say this violates 1NF but, just as before, we can select the contents of the table in a meaningful order using the event_datetime column.

I take Gus's point on update atomicity and agree that this poses a problem for the addresses structure. I suppose one could argue that the address_line_index, being a well defined domain, contains meaningful values and it would make no sense to insert a new row between 1 and 2. Having said that, this also suggests that "line 1" really means "the street address" part, and "line 2" means "the suburb part", which begs the question as to why they are being stored on different rows as opposed to separate attributes on a single row (returning us full circle to the null/combinatorial explosion discussion).

Ultimately, this "row per address line" structure seems like a kind of specialized entity-attribute-value structure. The specialization, whereby all attributes are for one entity and all attributes are related (common use of the term), makes this look less "EAV-like" than a more common, generic EAV table.

My own personal opinion? This table is in 1NF, just as the event_log table is in 1NF. Our ability to extract rows in a meaningful order does not violate the principle that the relation is unordered. I do not, however, think that the EAV pattern is a good one, and the point Gus made about update atomicity is certainly one reason for that.

edit: /necro