Maintaining referential integrity

Question

Given the schema:

MACHINE_TYPE { machine_type }
MACHINE { machine, machine_type }
SORT_PLAN { sort_plan, machine_type }
SCHEDULE { day_of_week, machine, sort_plan }

and the business rule:

A sort plan can be assigned to any machine of the same machine_type.

How do I enforce that, in SCHEDULE, the tuples referenced by machine and *sort_plan* have the same *machine_type*?

The schema can be changed, if necessary.

Answer 1

I'd use an insert trigger on the SCHEDULE table.

Answer 2

You could change the plan table so it does not have MachineType, and add a new table called machinePlan, that has a row for every machine that can use that plan, with the MachineId and the PlanId. Then derive MachineType for a plan from this new table's parent machine table instead of from the plan table itself.

Last, change the schedule table so that it's FK is back to this new MachinePlan table, instead of as you currently have it

MACHINE_TYPE { machine_type }
MACHINE { machine, machine_type }
SORT_PLAN { sort_plan}
MACHINE_SORTPLAN {machine, sort_plan }
SCHEDULE { day_of_week, machine_Sortplan }

This also has added benefit in that you are NOT irevocably typing the rules for a plan on which machine type they apply to. You are keeping this association separately, and can, if necessary, decide to use the same set of rules (the same plan, for machines of more than one machine type...