Referential Integrity with arrays or lists problem

[wintermutecz1]

I am having issues trying to find a way to enforce a constraint for a column that can store multiple values.

What I want to do is create a table that stores Sales. For each record in Sales, the broker(s) would be stored that were involved in the sale. The number of brokers involved could vary between 1 and X, so it would not be wise to create separate columns for Broker_1, Broker_2, Broker_3, etc. since there is no way to know before-hand exactly the number of brokers. Rather than creating dozens of Broker_# columns, I want to store all associated brokers in a single column either with a list or an array. The problem is that I don't want to allow a Broker_ID to be entered in this list (or array) if that Broker doesn't already exist in the table I use to store brokers.

Is there a way to get PostgreSQL to enforce a constraint (referential integrity) when the column could contain a list or array of Broker_IDs?

 

[flugh]

I would do a 'sales' table with a unique key, a 'brokers' table with each broker having a unique key (serial should be ok), then a 'sales2brokers' table with a 'sale_id' column and 'broker_id' column and relevant constraints on each of those 'sales2brokers' columns.

You can get a start on the foreign-key and constraint syntax here:

http://www.postgresql.org/docs/7.4/static/sql-createtable.html

----
JBR

 

[wintermutecz1]

Thanks for the prompt reply, flugh. I was thinking of doing something like that but had wondered if there might be a more elegant way of achieving this without the additional tables.

While not a major issue, I would need not only a sales2brokers table but also a sales2sellers table (to record the sellers involved in each sale). There are several other tables in which I need to constrain the contents of a column entry that contains a list (of undeterminate length) of foreign keys, so I could end up with quite a few columnx2columny tables.

The additional tables wouldn't be a concern per se, but the extra queries involved in searching them might be. I will have to think about this some more. I welcome any additional advice or thoughts...