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.

score 2 · Answer 1 · answered Dec 11 '08 at 14:14

2

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

answered Dec 11 '08 at 14:14

That was my first thought, but I feel like I'm missing something obvious. – Dave Dec 11 '08 at 14:17
Just make sure the trigger can handle multiple record inserts. – HLGEM Dec 11 '08 at 14:29
1

Triggers are scary. I try to avoid them unless absolutely necessary. However, some people do feel that you can never have enough of them. – BobbyShaftoe Dec 15 '08 at 15:44

score 1 · Accepted Answer · answered Dec 11 '08 at 14:25

1

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...

answered Dec 11 '08 at 14:25

Charles Bretana

143,358
22
150
216

This might be what I'm looking for. When sort plans are created, machines aren't specified, only machine type. – Dave Dec 11 '08 at 14:37
Then you would need to add machine type back into sortPlan, but still, when assigning a plan to a new machine, or creating a schedule, requiring that a record must be added to the MachineSortPlan table (as well as to schedule) would enforce your constraint – Charles Bretana Dec 11 '08 at 14:45
"...requiring that a record must be added to the MachineSortPlan..." Wouldn't that essentially require a trigger? How would I enforce that with just RI? – Dave Dec 11 '08 at 14:57
I'll go with this as the answer. The discussion convinced me that a trigger is probably the way to go. – Dave Dec 11 '08 at 15:49
Well using the schema I outlined would require that a record exist (or be added) to the MachineSortPlan .. You can;t add a new schedule with a FK to Machine_SortPlan until a machineSortPlan record is inserted for that FK to reference... – Charles Bretana Dec 11 '08 at 15:53

Maintaining referential integrity

2 Answers2