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.