Foreign Key Constraint: Uniqueness vs. Data Redundancy

Question

Say we have four tables (column names in parentheses):

• types (type_name)
• parameters (type_name, parameter_name)
• instances (instance_id, type_name)
• instance_parameters (instance_id, parameter_name, parameter_value)

In English: Types can define parameters. Instances of a type can set values for the predefined parameters of their type. We have (the obvious) FK constraints for all type_name as well as instance_id columns.

Now I would like to make sure that rows in instance_parameters reference existing parameter_names in parameters. A classical FK constraint is not possible because the type_name is implied by the instance_id and thus missing in order to reference a unique key in the parent table parameters.

How can I accomplish this without adding the redundant information of type_name to the instance_parameters table?

Answer 1

The way I understand the story, as close to the original schema as I could get it:

-- Type TYP exists.
--
type {TYP}
  PK {TYP}

-- Parameter PAR of type TYP exists.
--
parameter {PAR, TYP}
       PK {PAR}
       SK {PAR, TYP}

FK {TYP} REFERENCES type {TYP}

-- Instance INS of type TYP exists.
-- 
instance {INS, TYP}
      PK {INS}
      SK {INS, TYP}

FK {TYP} REFERENCES type {TYP}

-- Instance INS of type TYP, of parameter PAR (of that type),
-- has value VAL. 
--
instance_parameter {INS, PAR, TYP, VAL}
                PK {INS}

FK1 {INS, TYP} REFERENCES instance  {INS, TYP}
FK2 {PAR, TYP} REFERENCES parameter {PAR, TYP}

---

Most likely you would want to simplify:

-- Type TYP exists.
--
type {TYP}
  PK {TYP}

-- Parameter PAR of type TYP exists.
--
parameter {PAR, TYP}
       PK {PAR}

FK {TYP} REFERENCES type {TYP}

-- Instance INS of parameter PAR has value VAL.
-- 
instance {INS, PAR, VAL}
      PK {INS}

FK {PAR} REFERENCES parameter {PAR}

---

Note:

All attributes (columns) NOT NULL

PK = Primary Key
AK = Alternate Key   (Unique)
SK = Proper Superkey (Unique)
FK = Foreign Key