Dealing with a pair of nullable foreign keys which could “logically” be part of the identity

All we need is an easy explanation of the problem, so here it is.

Maybe I’m going about this the wrong way, but here goes. A bit of background:

I’m designing a system where users will select fields with a specified order. These fields are associated to a profile. The fields are selected from 2 different tables, one with system fields, and one with custom fields. Those fields would be stored in a table, but the original design actually does not define any schema relations between the field tables and the mapping table.

I want to actually map the 2 different tables properly, but I’m hitting a wall with how the mapping table should be defined. Here’s the original design and what seems "logical" to me.

Dealing with a pair of nullable foreign keys which could “logically” be part of the identity

How do I construct this mapping table? I feel like the foreign keys should be used as primary keys, but obviously each row would either have a nullable CustomFieldId or FieldId, making this impossible.

Do I give up on having the foreign keys as primary keys and add uniqueness constraints in the table?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You are missing an extra relation (table) here.

What you have is a polymorphic association, where you have a concept of a Field, which can either be a SystemField or a CustomField. There are a number of ways to do this, for example you could just use a bit flag, but a common one is to separate them into different tables, and use the Id of the base table along with a Type as both the PK of the child tables, and the foreign key back to Field.

Now you can simply have ProfileFields refer to the base table, it does not need to worry about which type a Field is.

What you need then are the following tables (pseudo-code):

CREATE TABLE Field (
  Id int IDENTITY,
  FieldType char(1) CHECK (FieldType IN ('S', 'C')),
  PRIMARY KEY (Id, FieldType),
 ...)
-- this represents any type of field

CREATE TABLE SystemField (
  Id int,   -- no IDENTITY
  FieldType char(1) CHECK (FieldType = 'S'),
  PRIMARY KEY (Id, FieldType),
  FOREIGN KEY (Id, FieldType) REFERENCES Field (Id, FieldType),
...)

CREATE TABLE CustomField (
  Id int,   -- no IDENTITY
  FieldType char(1) CHECK (FieldType = 'C'),
  PRIMARY KEY (Id, FieldType),
  FOREIGN KEY (Id, FieldType) REFERENCES Field (Id, FieldType),
...)

CREATE TABLE Profile ( .... )

CREATE TABLE ProfileFields (
  ProfileId int,
  FieldId int,
  FieldType char(1),
  PRIMARY KEY (ProfileId, FieldId, FieldType),
  FOREIGN KEY (ProfileId) REFERENCES Profile (Id),
  FOREIGN KEY (FieldId, FieldType) REFERENCES Field (Id, FieldType),
...)

Note that ProfileFields does not reference the two child tables.

You add a field by first adding to Field, then taking its IDENTITY value and adding it to the relevant table:

INSERT Field (FieldType, ...)
VALUES ('S', ... );

INSERT SystemField (Id, FieldType, ...)
VALUES (SCOPE_IDENTITY(), 'S', ... );

You could potentially remove FieldType from ProfileFields but then you would need a second unique key on Id in the base Field table.

You can also change the CHECK constraint into a lookup table, but I personally don’t see the point.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply