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

Tim Allwine tallwine at oreilly.com
Sat Oct 26 11:00:19 PDT 2002


And if you like databases and use MySQL check out the new MySQL 
Cookbook by Paul DuBois. It's the best MySQL book that I've ever 
read.

http://www.oreilly.com/catalog/mysqlckbk/

-Tim

Kevin Bingham wrote:
> 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