CODEWITHSUNDEEP
mysqldatabase
user1104759
user1104759

Reputation: 3

can 2 entities have 2 relationships

In database design, can 2 entities have 2 relationships among themselves? i.e for example there are 2 entities donor and admin.. there are 2 relationships 1. admin accesses donor details 2. admin can contact donor and vice versa can we join them with 2 relationships?

Upvotes: 0

Views: 121

Answers (1)

Medo42
Medo42

Reputation: 3821

Definitely, although how much sense it makes to model "accesses" and "contacts" relations in a database depends on your application. I'll stay with your example though and assume these relations are n to n. Here is how the SQL could look like (warning, syntax not tested):

CREATE TABLE admin (
    id int unsigned AUTO_INCREMENT PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE donor (
    id int unsigned AUTO_INCREMENT PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE admin_donor_access_details (
    id_admin int unsigned NOT NULL,
    id_donor int unsigned NOT NULL,
    PRIMARY KEY (id_admin, id_donor),
    CONSTRAINT FOREIGN KEY(id_admin) REFERENCES admin(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY(id_donor) REFERENCES donor(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE admin_donor_contact (
    id_admin int unsigned NOT NULL,
    id_donor int unsigned NOT NULL,
    PRIMARY KEY (id_admin, id_donor),
    CONSTRAINT FOREIGN KEY(id_admin) REFERENCES admin(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY(id_donor) REFERENCES donor(id) ON DELETE CASCADE ON UPDATE CASCADE
);

The two relations could also be expressed in a single join table with boolean flags, like this:

CREATE TABLE admin_donor (
    id_admin int unsigned NOT NULL,
    id_donor int unsigned NOT NULL,
    detail_access tinyint(1) NOT NULL,
    contact tinyint(1) NOT NULL,
    PRIMARY KEY (id_admin, id_donor),
    CONSTRAINT FOREIGN KEY(id_admin) REFERENCES admin(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT FOREIGN KEY(id_donor) REFERENCES donor(id) ON DELETE CASCADE ON UPDATE CASCADE
);

This will put some extra effort on your code because you need to determine whether to insert or update a row when adding a relationship, and whether to delete or update a row when removing a relationship, but in my opinion this is still a usable alternative.

Upvotes: 1

Related Questions