3

Option 1

I have a database schema that looks something like this:
https://dbfiddle.uk/JKCTjadD
enter image description here

  1. A Task belongs to a Service (like in ECS)
  2. A Task can be reassigned to another Service.
  3. TaskConfig tracks the reassignment history (and other configs), and the latest record (using inserted_on DESC) is the Service that a Task is currently assigned to.
  4. A Service has a unique constraint (service_name, environment). Environment has its own table but illustrated here as an Enum for brevity.
  5. A Task also has a unique constraint (task_name, environment).

The Problem

The environment in Task is duplicated/redundant: the Service associated with the Task already has the environment that the Task is in.

Option 2

I decided to move Service_id from TaskConfig into Task:
https://dbfiddle.uk/QB7k9hK3
enter image description here
I can then use the following query to fetch the latest pairs of (task_name, environment_name):

SELECT DISTINCT ON (task_name, environment_name) * 
FROM TaskConfig 
JOIN Task ON Task.id = Task_id
JOIN Service ON Service.id = Service_id
ORDER BY task_name, environment_name, inserted_on DESC

The problem

  1. I can no longer have a unique constraint on (task_name, environment) since environment is not in the Task table.
  2. A future developer could mistakenly select (task_name, Service_id) which will not result in unique (task_name, environment) pairs.

Help

How would you propose to resolve the issues above?
If there are no better ways, which of the 2 options above is the better schema?

1 Answer 1

2

If a task can belong only to one service, the second data model is the correct one. To enforce your constraint, you have to add a redundant copy of environment to task:

  • add a new column environment to task and fill it with the values from service

  • add a redundant unique constraint on service (id, environment)

  • define the foreign key from task to service using these two columns

Now you can add your constraint. Yes, this answer needs a redundant copy of environment, but there is no way to avoid that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.