Best way to enforce table participation

Question

I am not sure what will be the best way to enforce participation in the following database design (can be for SQL server, Oracle, mySQL etc.). I have two tables: Factory and Worker. I have created two rules for the design: 1) Each factory hires at least one (or many) workers. 2) Each worker works for exactly one and only one factory.

I have created an ERD for the design:

[Factory]-||--- hire ---|<-[Worker]

The FACTORY table looks like this:

Factory_ID (PK) Factory_Name

1 A
2 B
3 C

The WORKER table looks like this:

Worker_ID (PK) Worker_Name Factory_ID (FK)

1 Tom 2
2 Ann 1
3 Jan 1

In the worker table, the Factory_ID is the foreign key (FK) and it is set to NOT NULL because (the ERD said) each worker have to work for one factory and only one. This participation can be enforced by setting the FK (the Factory_ID field in the Worker table) to not null.

Here is my question, what is the best way to enforce the participation of - Each factory hires at least one workers? I.e. I need to make sure each factory_id have to be referenced in the worker table at least once. So now factory #3 does not hire anyone which violates the participation rule in the ERD.

I am wondering what will be the best way to enforce this participation?

Answer 1

1+ constraints on relationships are usually implemented at the application layer.

• Stored Procedure
• Triggers
• Business layer of the backend
• Front end
• Any combination of the previous

The problem is that there's a timing concept to anticipate: with this kind of restrictions you would need a worker for your factory but your worker needs a factory himself: this is just simply impossible to implement this rule a synchronous maner.

A solution could be to manage a sort of backoffice to add the elements separately (for administrative purpose) and then implement within the public front the appropriate restrictions to match the 1+ requirements you have. For example:

1. When removing a worker to a company, check that there's at least one left
2. Implementing a company replacement for when an employee changes his company, to ensure there's always one and only one company per employee.