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

[hanksdc at plug.org]

On Fri, 25 Oct 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".

I guess I need to clarify my thinking here. As I understand it, I think
what Codd, Date, and friends (and I'm firmly on their side of the
issue) are saying when they argue against multivalue fields is that, for
example let's say we have a database field (or more accurately a relation
attribute) of type (or domain) char(10). A multivalue database would allow
you to hold one or more 10-character strings for a single row (tuple),
which breaks stride with relational theory.

What I'm trying to say is, let's say I create a type (domain) which allows
as its values, triples of 10-character strings (I.e., a set of 3,
10-character strings). Let's call this data type '10-char-triple'. Along
with this datatype, I also create appropriate operators to manipulate,
access, and work with this datatype.

So having this datatype, I now create a table with a field (attribute) of
type '10-char-triple'. As long as my database ensures that I can't allow
multiple values of '10-char-triples' per row (tuple), I'm still within the
limits of relational theory. In this sense, each 10-char-triple is an
atomic unit, just as, for example, a 10-character string is an atomic
unit, despite bing made up of 10 characters.

Now, I side with Date, Pascal, Codd, et al., and as far as I can tell,
what I've outlined above is in line with what they profess as
theoretically sound relational design. What I gather from this
discussion, is the important principles here are that each attribute
of a relation is declared to be of a particular type, or domain. Also,
each tuple in that relation must allow 1, and only 1 value of that type
(domain) for the given attribute. It depends on what your types, or
domains are.

>
> 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."
>

And from my example, a single value of my 10-char-triple datatype would
consist of three 10-character strings, albeit accessed as an atomic unit.

At any rate I appreciate this discussion. If nothing else, it has helped
my to clarify my thinking in the area.

-- Dan Hanks