SQL - Two foreign keys that have a dependency between them

Question

The current structure is as follows:

Table RowType:    RowTypeID

Table RowSubType: RowSubTypeID
                  FK_RowTypeID

Table ColumnDef:  FK_RowTypeID
                  FK_RowSubTypeID (nullable)

In short, I'm mapping column definitions to rows. In some cases, those rows have subtype(s), which will have column definitions specific to them. Alternatively, I could hang those column definitions that are specific to subtypes off their own table, or I could combine the data in RowType and RowSubType into one table and work with a single ID, but I'm not sure either is a better solution (if anything, I'd lean towards the latter, as we mostly end up pulling ColumnDefs for a given RowType/RowSubType).

Is the current design SQL blasphemy?

If I keep the current structure, how do I maintain that if RowSubTypeID is specified in ColumnDef, that it must correspond to the RowType specified by RowTypeID? Should I try to enforce this with a trigger or am I missing a simple redesign that would solve the problem?

Answer 1

What you're having trouble with is Fourth Normal Form.

Here's the solution:

Table RowSubType:       RowSubTypeID
                        FK_RowTypeID
                        UNIQUE(FK_RowTypeID, RowSubTypeID) 

Table ColumnDef:        ColumnDefID
                        FK_RowTypeID
                        UNIQUE(ColumnDefID, FK_RowTypeID) 

Table ColumnDefSubType: FK_ColumnDefID   } compound foreign key to ColumnDef
                        FK_RowTypeID     }   } 
                        FK_RowSubTypeID      } compound foreign key to RowSubType

You only need to create a row in the ColumnDefSubType table for columns that have a row subtype. But all references are constrained so you can't create an anomaly.

But for what it's worth, I agree with @Seth's comment about possible over-engineering. I'm not sure I understand how you're using these column defs and row types, but it smells like the Inner-Platform Effect anti-pattern. In SQL, just use metadata to define metadata. Don't try to use data to create a dynamic schema.

See also this excellent story: Bad CaRMa.

---

Re your comment: In your case I'd recommend using Class Table Inheritance or Concrete Table Inheritance. This means defining a separate table per subtype. But each column of your original text record would go into the respective column of the subtype table. That way you don't need to have your rowtype or rowsubtype tables, it's implicit by defining tables for each subtype. And you don't need your columndefs table, that's implicit by the columns defined in your tables.

See also my answer to Product table, many kinds of product, each product has many parameters or my presentation slides Practical Object-Oriented Models in SQL.