Database design: two related tables in a Database both reference a third table. How to ensure the th-CodePudding

In my database I have three tables which look like these:

companies
 ------------ 
| company_id |
 ------------ 

agents
 ---------- ------------ 
| agent_id | company_id |
 ---------- ------------ 

customers
 ------------- ---------- ------------ 
| customer_id | agent_id | company_id |
 ------------- ---------- ------------

where the first id of every row is the primary key of the table.

These are the rules:

each company has many customers
each company has zero or many agents
each agent works for exactly one company
each agent deals with many customers
each customer buys from exactly one company (assume so)
each customer may be assigned an agent who deals with them

Property company_id in the customers table can be technically computed if the customer is assigned an agent (first we look for the agent_id of the customer and then the company_id of that agent). However if the company has no agents OR if the customer is not assigned an agent, then company_id has to be explicit in the customers table.

How can I design my database so that customers.company_id is coherent with agent.company_id with agent coming from customers.agent_id?

CodePudding user response：

You would define a unique key in agents combining the company_id and agent_id. Something like this:

alter table agents add constraint unq_agents_company_agent
    unique (company_id, agent_id);

Then you would define a foreign key from customers:

alter table customers add constraint fk_customers_agents
    foreign key (company_id, agent_id) references agents (company_id, agent_id);

[The above syntax is pretty generic, but it might depend on the database.]

This should ensure the conditions that you have specified.