SQL data type issue

Question

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

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

The primary key in Customer is Customer_ID and INT. It is related to the type of "Subscribed Clients" ??? in the table agent. So what should be the type of "Subscribed Clients"?

Any help would be greatly appreciated.

Answer 1

Assuming that a client can only be signed for no more than one agent, then you need a foreign key column in the client table that points to the agent table (instead of specifying from agent to client, as 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 client can be signed by several agents, then you need OMG Ponies answer with a table of links. You need to clarify for yourself what you need.)

Answer 2

You need a table that is between the CUSTOMER and AGENT tables, connecting them:

AGENT_CUSTOMERS

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

The type of data you are looking for means storing denormalized data, which would make the king's pain an attempt to obtain specific client values. Save yourself a headache by setting the right conditions.

Answer 3

re: "In any case, you cannot join an array of one value or at least not perform any actions." - HLGEM Dec 30 '10 at 18:11

I can and definitely always join the comma-separated list.

In SQL 2000, they introduced UDF, and I often use the Split function to take a comma-separated list and turn it into a table.

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

I used this function to search property lists (for real estate), and it usually worked pretty well.

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

SQL split function , 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 

Answer 4

Relations should always contain the same data type, so here you have not much choice.