Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: multi-valued field?

Re: multi-valued field?

From: Goh Boon Nam <gohbn_at_singnet.com.sg>
Date: 1997/01/07
Message-ID: <32D244BF.65D@singnet.com.sg>#1/1

Tony Vuong wrote:
>
> In article 537C_at_ibm.net, Steve Behman <sbehman_at_ibm.net> writes:
> > Kevin Kane wrote:
> > >
> > > Hi. Is there such a thing in DB2 as a multi-valued field? By this I mean
> > > that I have a column called, say, AUTOMOBILES. If a person has 2
> > > cars, then I would like the AUTOMOBILES field of his record to contain
> > > 2 entries, one for each car.
> > >
> > > So, can DB2 handle this, or am I just going to have to cat the
> > > individual values together into a character string with some kind of
> > > delimiter?
> > >
> >

Prima-facie, it would not be wise to do that unless there is some pressing reason to do so. What you describe above would generally be better handled by normalising the data and having two tables - one for person and another for person-car with a one to many relationship between them.

This will usually provide more flexibility - eg. you can queries more easily

eg. How many persons own more than one car?

select person, count(*)
from person-car
having count(*) > 1
do summation easily to

eg. How many persons own Honda's?

select *
from
person-car pc,
person p,
where pc.person-id = p.person-id
and pc.car-type = 'honda'

The only problem with normalisation is that SQL cannot easily de-normalise back the data eg. if you want a report to print only one line for each person and to list out all cars on the same line, then you probably need to do some procedural programming or create temporary tables to achieve it. Received on Tue Jan 07 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US