I’ve implemented Primary Child functionality using a third table as described by @ypercube here (and other posts). I chose this method because it’s database-agnostic, simple (no triggers or deferred constraints), and seems clean. Here’s my table structure:
I’m trying to implement two business rules with this structure. The first seems to work, but not the second.
Business Rule 1: PrimaryChild must be a Usable Child, denoted by the boolean field Usable in the Child table.
To achieve this, I added a Usable field to PrimaryChild and composite keys in Child and PrimaryChild composed of ChildID + ParentID + Usable. Here’s the structure:
I then placed a Check constraint on the Usable field in PrimaryChild so that it must be TRUE. This seems to work well.
Business Rule 2: Usable status depends on other state fields in Child: ControlsUsable1, ControlsUsable2, etc. (four ControlsUsable’s now, more may come).
This suggests the Usable field on Child should be calculated, and calculated fields apparently can’t be used in composite foreign keys. I can think of only one potential solution: nix the derived Usable value and use each of the ControlsUsable fields instead. But I’d rather not because it complicates current and future query logic and means the composite foreign key (that seems to be necessary to achieve Business Rule 1) will consist of at least 6 fields in both Child and PrimaryChild.
Can you suggest a better solution? Any ideas would be appreciated.
Usablein Child table not a calculated column but a normal one and add a constraint, something likeCHECK (Usable = ControlsUsable1 OR ControlsUsable2 OR ...), whatever the business rule is ? – ypercubeᵀᴹ Jun 09 '22 at 07:05Usablewould be derived from theControlsUsablevalues we feed the table, simplifying data prep. And inversely, that constrainingUsablewill require upserts to include values forUsableandControlsUsable…which complicates data prep. Do I have this bassackwards? – DoctorYes Jun 10 '22 at 15:49