sql - How do I ensure integrity between unrelated tables?

Question

I just recently started learning database design, and I'm am working with Oracle 11G and SQL Developer.

I have these 3 business rules for a DB:

• Each OFFICER must enroll in one and only one INSURANCE COMPANIES. each INSURANCE COMPANY may enroll one or more OFFICERS
• Each INSURANCE COMPANY must provide at least five different types of INSURANCE TYPES. Each TYPE OF INSURANCE may be provided by up to 4 INSURANCE COMPANIES or none at all
• Each INSURANCE TYPE may be subscribed to by one or more OFFICERS. Each OFFICER may subscribe to up to FIVE different INSURANCE COVERS provided by the same company.

. . .

so far, so good, i came up with five TABLES (INS_COY, OFFR, INS_TYPE, PROVIDE, and SUBSCRIBE). PROVIDE and SUBSCRIBE came about as composite tables since the relationships between INS_COY and INS_TYPE, and OFFR and INS_TYPE are both M:M relationships.

PK and FK attributes for each of the table is as below:

INS_COY TABLE
coy_id - PK

OFFR TABLE
offr_id - PK
coy_id - (FK referencing INS_COY.coy_id))

INS_TYPE TABLE
type_id - PK

PROVIDE
coy_id and type_id - (composite PK)
coy_id - (FK referencing COY.coy_id)
type_id - (FK referencing ins_type.type_id)

SUBSCRIBE
naf_no and type_id - (composite PK)
naf_no - (FK referencing offr.offr_id)
type_id (FK referencing ins_type.type_id)

.
.
.

the tables have been sucessfully created, and sample data inserted.

so, the problem is - on the SUBSCRIBE TABLE, HOW DO I ENSURE INTEGRITY THAT THE TYPE_ID ATTACHED TO AN OFFR_ID IS AN INS_TYPE PROVIDED BY THE COY HE IS ENROLLED IN?

sample data tables

i.e ...from the tables, "offr 4250" is enrolled in "coy 1", and "coy 1" doesn't provide "ins_type 13", however, because there's no constraint to check this, "offr 1" is subscribed to "ins_type 13" on the SUBSCRIBE TABLE.

Answer 1

You can do it using controlled redundancy and composite FK constraints:

CREATE TABLE offr (
    offr_id INT NOT NULL,
    coy_id INT NOT NULL,
    PRIMARY KEY (offr_id),
    FOREIGN KEY (coy_id) REFERENCES ins_coy (coy_id),
    UNIQUE KEY (offr_id, coy_id)
);

I added a composite unique key (offr_id, coy_id) to support a composite FK constraint on the subscribe table.

CREATE TABLE provide (
    coy_id INT NOT NULL,
    type_id INT NOT NULL,
    PRIMARY KEY (coy_id, type_id),
    FOREIGN KEY (coy_id) REFERENCES ins_coy (coy_id)
);

The composite primary key here is perfect for a composite FK constraint on the subscribe table.

CREATE TABLE subscribe (
    naf_no INT NOT NULL,
    coy_id INT NOT NULL,
    type_id INT NOT NULL,
    PRIMARY KEY (naf_no, type_id),
    FOREIGN KEY (naf_no, coy_id) REFERENCES offr (offr_id, coy_id),
    FOREIGN KEY (coy_id, type_id) REFERENCES provide (coy_id, type_id)
);

Overlapping composite FK constraints will ensure that an officer can only subscribe to insurance offered by the company he/she is enrolled in. coy_id is logically redundant but required for integrity and there's no risk of update anomalies due to the FK constraints.

Alternatively, you could use triggers to check that the values are related via inner joins:

CREATE TRIGGER check_subscribe BEFORE INSERT OR UPDATE ON subscribe
FOR EACH ROW
WHEN NOT EXISTS (
    SELECT 1
    FROM offr
    INNER JOIN provide ON offr.coy_id = provide.coy_id
    WHERE offr.offr_id = new.naf_no AND provide.type_id = new.type_id
)
RAISE_APPLICATION_ERROR (num => -20000, msg => 'Officers can only subscribe to types provided by their company');

Disclaimer: I was unable to test this on SqlFiddle and don't have Oracle installed, but hopefully it'll point you in the right direction.