Creating a multi-Valued table in Oracle ver > 8.15 using SQL PLUS

[mike2312]

I hope this is the correct thread for this question. I am trying to implement a database in Oracle 8.16i. One particular table needs to have a couple columns that can be multi-valued.

For example:
Lets say I want to create a table called employees. In it I want all pertinent employee information, eg Name, address, phone number.

Continuing down this orwellian path I might want to have several phone numbers for an employee.

So employee 1 might be John Doe, with a ssn of 123-45-6789 and three phone numbers, 555-555-5555, 444-444-4444, etc.
However pther employees might only have ONE phone number. Others might have 20.

Do I need to create a seperate User Defined Type/Object and then make that a table out of those types and then nest that table?? Is there another way to do this?

 

[jocave]

The standard approach would be to have a separate detail table for this sort of thing, i.e.

Table emp has a primary key empno
Table emp_phone has a column empno, a foreign key to the emp table, and a phone number column

If an employee has only one phone number, there would be only one row in emp_phone for her employee number. If another employee has 20 phone numbers, there would be 20 rows in emp_phone for his employee number.

You could also define a phone number column that was an array of varchar2 (assuming that's how phone numbers are stored), but I tend not to be a big fan of that sort of setup.

 

[sem]

Objects in Oracle IMO are still not mature enough to be recomended for a wide use. I also think that creating separate table may be better (pure relational approach). You may also choose 1 number as primary and place to that separate table only additional numbers.

Regards, Dima

 

[amarkiew]

Agreed that a seperate relational table is the way to go. Supports first normal form.
But I would put all numbers on the same table and have an attribute marking primary contact number (or a ranking of contact numbers to be able to sort by importance) if desired.

This makes coding much easier having all numbers in the same table. No need for unions and decodes to make it look like the numbers are in the same table when you need them to be.

 

[ganotedp]

A key point is that parties (persons/organizations) don't "just" have several phone numbers. Phone numbers have various functions, relative locations, and attached devices; for example: home fax, office fax, office voice, home voice, mobile voice, mobile fax, etc. A single, multi-valued field is incorrect. In my opinion, the QND (quick-and-dirty) approach for a personal-use-only application is to create a PHONE_TYPE attribute on the EMPLOYEE_PHONE table. It's a "junk" approach because it rolls several elements into a multi-use field. The correct approach for a real application is to discretely define the various factors that characterize the phone usages and handle them as separate elements. An organization might want to get a list of all sales reps' mobile voice phones#, and that would be difficult with an uncontrolled, poorly-planned PHONE_TYPE attribute.

 

[KenCunningham]

Oh for the old Prime Information (and Universe, Unidata etc) multi-valued fields eh? Priceless!