Re: multi-valued field?

Tony Vuong wrote: > > In article 537C@ibm.net, Steve Behman <sbehman@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(*) > 1do 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.