CODEWITHSUNDEEP
mysqlsqldatabaseerd
Pouissante
Pouissante

Reputation: 128

Cardinality if a relationship depends on another one

I've a table which has a relationship with two other tables, the catch is that one of the two foreign keys will be null.

The thing is that a picture can either belong to a treatment or a client and a treatment belongs to a client. The problem is, if I only link to a client, i don't know which picture is tied to a treatment and which not.

Tables to join

Solution? :

Solution

Thank you for any idea

Upvotes: 0

Views: 133

Answers (1)

GMB
GMB

Reputation: 222582

One way to adress this would be to create to separate mapping tables to represent the relationships between, on the one hand pictures and treatments, and on the other hand pictures and clients.

create table pictures_treatments(
    idPicture int,
    idTreatment int,
    primay key(idPicture),
    foreign key (idPicture) references t_pictures(idPicture),
    foreign key (idTreatment) references t_treatment(idTreatment)
);

create table pictures_clients(
    idClient int,
    idTreatment int,
    primay key(idPicture),
    foreign key (idPicture) references t_pictures(idPicture),
    foreign key (idClient) references t_client(idClient)
);

With this set-up in place, you can easily recover the picture associated with a treatment or a client with two left joins:

select t.*, p.picPath
from t_treatments t
left join pictures_treatments pt on pt.idTreatment = t.idTreatment
left join t_pictures p on p.idPicture = pt.idPicture;

select c.*, p.picPath
from t_clients t
left join pictures_clients pc on pc.idClient = c.idClient
left join t_pictures p on p.idPicture = pt.idPicture

Upvotes: 1

Related Questions