SQL data type question

Question

I'm having some trouble choosing an appropriate data type for an attribute in a simple Oracle SQL database.

Here's my situation, I have two tables - Customer and Agent. One of the attributes in my Agent table is called Signed Customers, I need it to hold a set of integers (customer numbers) as a sort of array.

The primary key in Customer is Customer_ID and is INT. It has a relationship to "Signed Customers" type ??? in table Agent. So what should the type of "Signed Customers" be?

Any help would be greatly appreciated.

Answer 1

You need a table that sits between the CUSTOMER and AGENT tables, linking them together:

AGENT_CUSTOMERS

• AGENT_ID (primary key, foreign key to AGENT.AGENT_ID)
• CUSTOMER_ID (primary key, foreign key to CUSTOMER.CUSTOMER_ID)

The data type you seek means storing denormalized data, which would make for a royal pain to try to retrieve specific customer values. Save yourself the headache by setting things up properly.