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

Kevin Bingham kevin at oreilly.com
Fri Oct 25 14:28:19 PDT 2002


Wow. With all the database foo, it sounds like we could have more talks on 
db's.

-Kudos!


At 01:27 PM 10/25/2002, Eric Eisenhart wrote:
>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



More information about the talk mailing list