Co-worker argues that storing a serialized string of data in a database does not violate 1NF

1NF requires fields to be atomic; that is, it should represent only a single value.

He says because he doesn't expect for the data to be searchable, or开发者_如何学JAVA readable then it is not in violation of normal form and that the value represents a single object.

Is he right?

Define "atomic".

The recentmost advances in theory reveal that the concept of "atomicity" on which the definition of 1NF (as typically understood) relies, is vague, and probably undefinable altogether.

For example, a coordinate on a map, is that an "atomic" value ? Usually, such a value has clearly visible 'X' and 'Y' components, and the value of those components can be "drawn out of" your "atomic" value. And if something can be "drawn out of" something else, then it is suspect to claim that that "something else" is "atomic" in the usual sense of the word (i.e. not further decomposable).

Is using a value of type "coordinate on a map" then in violation of 1NF, for precisely that reason ? That position is hard to maintain.

For such reasons, a single string holding a list of CSV's, does not formally violate 1NF. That is not to say that actually designing your databases on this basis is a very good idea. Most of the time, it won't be. But formally speaking, it does not violate 1NF (or whatever is left of it).

A string is a single value. The fact that it can be split into smaller strings doesn't mean you are violating 1NF. If you are encoding a lot of information into strings then you may not be taking best advantage of your DBMS features (i.e. the ability to query the data and enforce constraints on it) but that's a different question.

The problem is that a single value can often actually be decomposed into separate values depending on the context (e.g., a varchar can be many char values, and a floating point number can be two separate numbers). If the serialized data is not relevant to the relation that's represented by the table, then it may be considered 1NF.

An Address field can contain a street name and city in a generic ContactInfo table, but the field wouldn't be considered atomic in an Addresses table that would have separate attributes for street name, city, ZIP, etc.

Yes, your cow-orker is right.

The current wisdom is that a single value can be arbitrarily complex. It can even be a table. (In Chris Date's books, look for "relation-valued attribute".) Dates and timestamps are single values, but they both have internal structure.

But if a type does have internal structure, the dbms either ignores that internal structure (as SQL does if you SELECT CURRENT_TIMESTAMP) or it provides functions that operate on that internal structure (as SQL does if you SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP)).

The key is that the user doesn't have to write any procedural code to manipulate the contents of that internal structure. Either the dbms provides those functions, or a database designer who creates new types provides those functions.