Database 1NF vs. multivalue fields (was: Re: NBLUG Library ?)

[Eric Eisenhart]

On Thu, Oct 24, 2002 at 10:17:09PM -0600, hanksdc at plug.org wrote:
> > Foos
> > ----------
> > id  |int
> > name|varchar
> > bar |multi-value [one, two, three]
> 
> While I'd need to look at the MARC format a little more to fully
> understand this, a design much like this could still adhere to the
> relational model (and yes, still be 'proper'). Essentially you would have
> to design a user-defined type (let's call it multi-value-triple), along
> with the appropriate operators to manipulate that type, as well as
> means to enforce the allowable domain of values possible for that type.
> Postgres allows you to do this. But despite the apparent lack of atomicity,
> such a design would still be in 1st normal form, and hence, faithfully
> adhere to the relational model, which prescribes that each table be at
> least in 1NF.

<RANT>

It can't possibly follow first normal form.

Or you don't understand my example or what is meant by "multivalue".

First normal form specifically and singularly forbids multi-valued fields.

"In First Normal Form, each column must contain only a single value and each
row must contain the same columns."

There's 2 rules, and multi-valued fields are, very precisely, the primary
thing forbidden by first normal form.  (the same columns issue is,
essentially, a way to avoid a workaround that creates the same issue)

Typically, when normalizing a database, you worry about properly eliminating
things like having "author1" and "author2" in the same table (the second
half of first normal form); but that's because most RDBMS systems simply
don't have a mechanism to violate the first rule of first normal form more
directly.

(In other words, please expand on how non-atomic data can be atomic and how
you can have a design that violates the one rule of 1NF while still adhering
to 1NF.  I need more detailed information on what you mean in order to
accept this.)

It's like trying to say that you have a six-legged quadruped.  It might be a
good idea.  It might work great.  It might be far better than the
conventional four-legged quadrupeds, but it's simply *either* got six legs
*or* it's a quadruped, not both; it's a matter of the definitions.

Databases that aren't 1NF can be very useful and interesting on occassion. 
They're not 1NF, though.

> Extending this idea, lots of complex data could be considered atomic, as
> long as the internal representation of the data type is invisible to the
> user, and as long as an 'object' of the data type can be manipulated only
> by operators defined for that type. In that sense, is it atomic.

Yes. My last example involves atomic data stored in 1NF implementing the
same level of complexity as a "multivalued" field would implement, but
without creating a whole new set of operators and preserving the ability to
do set based operations.

> More on this can be found in the first chapter of Fabian Pascal's
> excellent book, 'Practical Issues in Database Management'. (Also see
> www.dbdebunk.com for more of this type of discussion).


http://www.dbdebunk.com/multivalue.htm -- Fabian Pascal specifically argues
against multivalued tables, including this statement:
] The multivalue approach has been around forever and Codd declared it to be
] in violation of relational principles years ago. It is neither new, nor a
] replacement of RM, nor a solution to anything.

> A lot of argument today against the relational model is its perceived lack
> of support for complex data, but in this light I don't think that
> argument holds water.

I think it's a misperception based primarily on misunderstandings of the
relational model and weaknesses of particular implementations. 
(Particularly that most current RDBMS systems tie the logical relational
model directly to the physical storage mechanisms)

> > DB without multi-valued fields, doing real multi-valued stuff:
> >
> > Foos
> > ----------
> > id  |int
> > name|varchar
> >
> > Bars
> > ----------
> > id  |int
> > name|varchar
> >
> > FooBar
> > ----------
> > foo |int
> > bar |int


</RANT>

-- 
Eric Eisenhart                                  eric-dot-sig at eisenhart.com
Perl, SQL, Linux and Web            ^               IRC: Freiheit at freenode
Coder, Sysadmin and geek           /e\                AIM: falsch freiheit
http://eric.eisenhart.com/         ---                       ICQ: 48217244