Relationship table

[Anonymous]

Hi all,

I need a suggestion on how to implement the relationship table which would allow me to get information like "{Client A} {has a relationship manager called} {Name B}".

So, I have:

- a table Clients (cli_id, employee_id, etc)

- a table Employees (employee_id, etc.)

- a table Responsibilities (cli_id, relationship_type, related_relationship_id)

The Responsibilities table defines the relationships between the cli_id and another cli_id or employee_id. The field Related_Relationship_Id can have either the code of cli_id or employee_id.

Any suggestion on how to implement this is very welcome.

[Gysbert_Wassenaar]

Are the relations between client and employee that are registered in the Clients table also registered in Responsibilities table?

[Anonymous]

No, the Responsibilities table has just 3 fields:

- cli_id which is the primary key which connects it to the Clients table

- relationship_type which has text values like " has a relationship manager called "

- related_relationship_id which might contain codes from Clients or from Employees

[Gysbert_Wassenaar]

What kind of relationship is there between clients and employees? Why aren't they also registered in the Responsibilities table?

Actually it doesn't matter. Add those relationships to the Responsibilities table so you can drop the employee_id from the clients table. That way all the relations between clients and employees are in the Responsibilities table and you can associate all the tables through that table.

[Anonymous]

So you say to add the Employee_ID to the Responsibilities table based on the Client_ID?

I can do that, but I don't see how it will help.

[Gysbert_Wassenaar]

That will link your tables like this:

Client->Responsibilities on cli_id; Responsibilities->Employess on employee_id

Clients: cli_id, ...other fields...

Responsibilities: cli_id, relationship_type, employee_id, ...other fields...

Employees: employee_id, ...other fields...

[Anonymous]

Yes, that can be done. But I would risk to lose data in case if the responsibility for a given cli_id was not defined in the Responsibilities table.

But this is secondary, in my opinion.

The primary problem is that the table Responsibilities has 2 fields (cli_id, related_relationship_id) which point to the same field (cli_id in the Clients table).

This case is worsened by the fact that the related_relationship_id can point to 2 different fields: cli_id and employee_id. But this does not generally change the primary problem.

Therefore, I can't create a data model which would allow me to create a table with information like "Client A has a manager called Client B".

[Anonymous · Accepted solution]

After all, I decided to join keep some fields from the Clients and Employees table to the Responsibilities table. So, I have a duplication in my data model (Clients -> Responsibilities -> part of Clients). But this approach allows me to achieve my goal of displaying info like "Client A has a manager called Client B".