Assuming we have the following table: Item, Parent, Child and Parent is the parent of child.
The item can either belong to a parent or child and not both.
I have other tables that are similar to Item and they too can belong to either a Parent or Child.
Should I simply just add 2 nullable FK to them?
Can I enforce that either a Parent or Child must exists using the db?
The solution you outlined is one valid option - assuming that an item can only belong to a single person at any given time.
In PostgreSQL you can enforce mutual exclusion between the two fk columns with a simple CHECK constraint:
either a Parent or Child must exists
... you can add a simple CHECK constraint:
CHECK (a IS NOT NULL OR b IS NOT NULL)
Would demand at least one NOT NULL column - but also allow that both parent_id and child_id exist. If you want to disallow that, too, make it:
CHECK (a IS NOT NULL AND b IS NULL OR b IS NOT NULL AND a IS NULL)
