0

I have entities Workflows, Steps and Fields. They relate to each other like this:

  • A Workflow has many Steps and Fields.
  • A Step has many Fields.

A Field can belong to either a Workflow or a Step but not to both at once. My first option is to add two foreign key columns to Field where one will always be null. So the fields table will have the following columns:

Fields
- id
- name
- step_id (FK to steps)
- workflow_id (FK to workflows)

This allows to enforce uniqueness of the name in the scope of the parent (by creating a unique index on the parent ID and the name) as well as use foreign key cascading deletes ensuring any time a Workflow or Step is deleted the related Fields will be deleted as well. However, one column is always null and if I introduce another parent of Field in the future it would require adding yet another FK column.

In an attempt to normalize this, I can set up join tables and express the relationship in another way:

Fields
- id
- name

WorkflowFields
- workflow_id (FK to workflows)
- field_id (FK to fields)

StepFields
- step_id (FK to steps)
- field_id (FK to fields)

Now I don't have any null values but I lose the ability to enforce uniqueness and cascade the deletes (when a Workflow or Step is deleted, the join record WorkflowField or StepField will be deleted but not the actual Field record itself).

  • How do I handle the uniqueness requirement? Should name be a column on the join table instead of the fields table? This feels wrong but I don't see another way.

  • How do I handle cascading deletes? I want the corresponding Field to be deleted any time the Workflow or Step or any of the join models are deleted. Are database triggers my only option here?

  • Is there a better alternative or are these the options I'm limited to?

user4157124
  • 2,809
  • 13
  • 27
  • 42
user1032752
  • 751
  • 1
  • 11
  • 28
  • What is a field? If it's what I think it is (a descriptor), then it makes more sense to include descriptor elements in the Workflow and Step entities. Entity names should be singular. – Gilbert Le Blanc Jun 29 '23 at 23:03
  • @GilbertLeBlanc - a field is another entity that has a name, a value and a few other columns. Both Steps and Workflows can have multiple fields. (This is in the context of a Rails app where class names are singular but table names are plural.) – user1032752 Jun 29 '23 at 23:32
  • Okay. Have separate field entities, one for the workflow and one for the step. This eliminates the need for a null foreign key. The application code can handle name uniqueness. – Gilbert Le Blanc Jun 30 '23 at 09:55

2 Answers2

2

Another way of stating the problem is a step may be used exactly once, ergo:

Add columns to field to record where it's used.

create table field (
  id int not null primary key,
  -- other columns
  workflow_id int references workflow,
  step_id int references step,
  constraint overuse check (workflow_id is null or step_id is null)
)

The constraint ensures the field is used in either a workflow or a step, but not both.

Both FK's being null is OK - it just means the field is defined but not currently used.

It's fine to add more parents in the future by adding more FK columns - that is reflecting reality.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
1

Have a look at MySQL: Two n:1 relations, but not both at once.

Basically, it proposes to introduce a table containing a collection of fields (1 to many). WorkFlow and Steps will have as foreign-key the primary-key of this field-collection-table (one-to-one). Every time a Workflow (or a Step) is created, a new FieldCollection-entry is created (the creation always happens together, FieldCollection can be compared with a parent-class in OO-Programming). .

FieldCollection
 - fieldCollection_id

Field
 - field_id
 - fieldCollection_id

Workflow
 - workflow_id
 - fieldCollection_id

Steps
 - steps_id
 - fieldCollection_id
NIC
  • 69
  • 3
  • Thanks for your help! That's a interesting alternative but unfortunately don't address the cascading deletes. I would still need to manually handle deletion of the `FieldCollection`. – user1032752 Jul 05 '23 at 16:57