How to design schema many to many relations

Question

I have table of customer and table of policies and table of coverage.

• Each customer have 1 or more policies
• Each policy have 1 customer
• each policy have 1 or more coverage and each coverage appear in 1 or more policies

My problem is when I have policy with multiples coverage I don't know how to implement it in the schema so when I open policy I want the ability to add multiple coverage to my policy.

How the schema will look like?

Best regards to all

Answer 1

From my point of view, it is simpler to show than explain. This is Oracle syntax, but never mind that. I used only necessary primary and foreign key columns, just to illustrate the problem.

Customer is simple:

SQL> create table customer
  2    (id_customer number primary key);

Table created.

Policy has a foreign key constraint that points to customer:

SQL> create table policy
  2    (id_policy   number primary key,
  3     id_customer number references customer
  4    );

Table created.

Coverage is also simple:

SQL> create table coverage
  2    (id_coverage number primary key);

Table created.

This is what bothers you: how to store policies with multiple coverages - in a separate table! whose columns make foreign key constraints, pointing to appropriate tables, while its primary key is composite & made up of both columns:

SQL> create table policy_x_coverage
  2    (id_policy   number references policy,
  3     id_coverage number references coverage,
  4     --
  5     constraint pk_pxc primary key (id_policy, id_coverage)
  6    );

Table created.

SQL>