Unique via seperate tables

From: AcCeSsDeNiEd <dillon_at_rm_accessdenied.darktech.org>
Date: Mon, 27 Jan 2003 17:47:01 +0800
Message-ID: <s7v93vok90ff0eddbqrkc47b93j853i28e@4ax.com>

Ok, this is gonna sound stupid and might be impossible but I'm gonna give it a shot anyway.

I have a clients and agents.

Because there is a possibility that clients may be transferred form agent->agent without any limits, I need to capture the 'history' of transfers.

I have therefore created a client-agent relationship table.

In the client table, there is no 'agent' field indicating this client belongs to which agent 'cos this is kept in the client-agent relationship table.

Every client has a 'social security number' which is unique. Problem is, different agents may serve the same client but there is no way a single agent may have 2 records of the same client.

Since I have separated the 'agent_id' from the client table, there is no way I can set a unique on the 'agent_id' and 'social_secure_no'.

Is there any solution?
Maybe doing something stupid like setting a unique via different tables? Or is this just a design problem?

To e-mail me, remove "rm_" Received on Mon Jan 27 2003 - 03:47:01 CST