Many to many

In the previous post we created a relation one-to-many between two tables, and probably that was not the best solution, because this means that a contact could have as many interests as it wants (and this is ok) but an interest should be owned by just one contact.

A better one would be estabilish a many-to-many relation between them.

To implement a many-to-many relation we use a joining table, that stores the foreign keys for both tables.

So, we redesign the interest table in this way:

create table interest (
id number(*,0) not null,
description varchar2(20 byte) not null,
constraint interest_pk primary key (id)

And add a couple of items to it:

insert into interest (id, description)
values(1, 'trekking');

insert into interest (id, description)
values(2, 'reading');

Now this interests are not related with a specific contact (no FKs is in the table), but could be shared among many of them.

The joining table is:

create table contact_interest
contact_id number(*,0) not null,
interest_id number(*,0) not null,
constraint contact_fk foreign key (contact_id) references contact(id),
constraint interest_fk foreign key (interest_id) references interest(id)

No actual data in it, just FKs.

Adding trekking and reading to the number of the interests for the contact identified by id 1 is done in this way:

insert into contact_interest (contact_id, interest_id)
values(1, 1);

insert into contact_interest (contact_id, interest_id)
values(1, 2);

Having created the foreign key constraints, we can't "cheat". If we try to add an interest that is not in the table:

insert into contact_interest (contact_id, interest_id)
values(1, 99);

leads to an error.

A fun basic book on SQL: Head First SQL.

No comments:

Post a Comment