SQL data type 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.

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.

Assuming a customer can only be signed to at most one agent, then you need a foreign key column in the customers table that points to the agent table (rather than pointing from agent to customer, as your question suggested):

CREATE TABLE agents (
    agent_id int PRIMARY KEY,
    ... more columns ...
);

CREATE TABLE customers (
    customer_id int PRIMARY KEY,
    agent_id int REFERENCES agents,
    ... more columns ...
);

(If, however, a customer can be signed to multiple agents, then you need OMG Ponies' answer with a linkage table. You need to clarify for yourself what you need.)

re: "You can't join to an array from a single value anyway or at least not in any type of performant way." – HLGEM Dec 30 '10 at 18:11

I can and definitely do join to a comma-seperated list all the time.

In SQL 2000, they introduced UDFs, and I frequently use a function called Split in order to take a comma seperated list and turn it into a table.

With SQl 2005 and Newer, you can CROSS APPLY this function in order to join multiple rows (with a comma-seperated list as a single column) in order to denormalize this to a proper format.

I used this functionality for looking up property-lists (for real estate) and it generally worked well enough.

SELECT * FROM PropertyLists CROSS APPLY [OLReweAbf].[dbo].[udfSplit] (PropertyLists.propertyList,',')
WHERE PropertyLists.Area = 104

SQL Split Function, by herbee:

CREATE FUNCTION [dbo].[udfSplit](@text nvarchar(max), @delimiter char(1) = ‘ ‘)
  RETURNS @Strings TABLE ( position int IDENTITY PRIMARY KEY, value nvarchar(max) )
AS
BEGIN
    DECLARE @index int
    SET @index = -1
    WHILE (LEN(@text) > 0)
    BEGIN
        SET @index = CHARINDEX(@delimiter , @text)
        IF (@index = 0) AND (LEN(@text) > 0)
        BEGIN
            INSERT INTO @Strings VALUES (@text) BREAK
        END
        IF (@index > 1)
        BEGIN
            INSERT INTO @Strings VALUES (LEFT(@text, @index – 1))
            SET @text = RIGHT(@text, (LEN(@text) – @index))
        END
        ELSE
            SET @text = RIGHT(@text, (LEN(@text) – @index))
    END
    RETURN
END

Relationships must always hold the same data type, so, you don't have much choice here