Re: Unique via seperate tables

AcCeSsDeNiEd wrote:
>
> 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.

Good.

>
> 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.

Good.

>
> 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.

Why do you think that is a problem? In the client_agent table the primary key could be multiple attributes, specifically both the client ID and the agent ID can be part of the key.

>
> 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'.

Not in the client table. this unique contraint goes on your client_agent table.

>
> 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_"

You started by proposing a separate table, then you backed away from it. There is no problem with proposing additional tables to create the functionality you need. May I suggest that you have two tables with nearly identical columns, one that contains the current (active) assignments. and a second one that keeps the history by storing inactive assignments. Columns of effective dates and a few triggers to move things around will solve you problem fine.

--
Ed Prochak
Magic Interface, Ltd.
440-498-3700(office)
Computer consulting, database and web services.