Option 1
I have a database schema that looks something like this:
https://dbfiddle.uk/JKCTjadD

- A
Taskbelongs to aService(like in ECS) - A
Taskcan be reassigned to anotherService. TaskConfigtracks the reassignment history (and other configs), and the latest record (usinginserted_on DESC) is theServicethat aTaskis currently assigned to.- A
Servicehas a unique constraint (service_name,environment). Environment has its own table but illustrated here as an Enum for brevity. - A
Taskalso 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

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
- I can no longer have a unique constraint on (
task_name,environment) sinceenvironmentis not in theTasktable. - 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?