Prevent Cycles and Multiple Cascade Paths

Question

I have the following SQL tables:

create table dbo.Companies (
  Id int identity not null constraint primary key clustered (Id),   
);

create table dbo.Workers (
  Id int identity not null constraint primary key clustered (Id),  
  CompanyId int not null,
);

create table dbo.Evaluations (
  Id int identity not null constraint primary key clustered (Id),       
  CompanyId int not null,
  WorkerId int not null 
)

And the following constraints:

alter table dbo.Workers
add constraint FK_Workers_CompanyId foreign key (CompanyId) references dbo.Companies(Id) on delete cascade on update cascade;

alter table dbo.Evaluations
add constraint FK_Evaluations_CompanyId foreign key (CompanyId) references dbo.Companies(Id) on delete cascade on update cascade,
    constraint FK_Evaluations_WorkerId foreign key (WorkerId) references dbo.Workers(Id) on delete no action on update no action;

I am using Entity Framework and when I try to insert a record I get the error:

Additional information: Introducing FOREIGN KEY constraint 'FK_dbo.Workers_dbo.Companies_CompanyId' on table 'Workers' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Should I change the constraint or should I change my database design?

Let me explain why I designed my database this way:

1 - The CompanyId in Workers table indicates the Company where the Worker Currently works.

2 - The CompanyId in Evaluation indicates where the Worker worked when that evaluation was made ... And the WorkerId obviously indicates the worker that was evaluated.

To me all this makes sense but maybe I am wrong ...

Answer 1

To be more explicit:

CREATE TABLE dbo.EventTypes (
      EID INT PRIMARY KEY IDENTITY(1,1)
    , EventDescription VARCHAR(40)
);
INSERT INTO dbo.EventTypes (EventDescription) VALUES ('Hired');
INSERT INTO dbo.EventTypes (EventDescription) VALUES ('Evaluated');

These will be used and evaluated to determine what happened, the when is below.

CREATE TABLE dbo.Companies (
      CID INT PRIMARY KEY IDENTITY(1,1)
    /* ... */
);
CREATE TABLE dbo.Workers (
      WID INT PRIMARY KEY IDENTITY(1,1)
    /* ... */
);

CREATE TABLE dbo.History (
      HistID INT PRIMARY KEY IDENTITY(1,1)
    , TS DATETIME NOT NULL DEFAULT GETDATE()
    , CompanyID INT NOT NULL FOREIGN KEY REFERENCES dbo.Companies(CID) ON UPDATE CASCADE ON DELETE CASCADE
    , WorkerID INT NOT NULL FOREIGN KEY REFERENCES dbo.Workers(WID) ON UPDATE CASCADE ON DELETE CASCADE
    , EventTypeID INT NOT NULL FOREIGN KEY REFERENCES dbo.EventTypes(EID) ON UPDATE CASCADE ON DELETE CASCADE
    /* ...other generic history-traits... */
);
CREATE NONCLUSTERED INDEX Idx_History_TS ON dbo.History (TS) INCLUDE (CompanyID, WorkerID, EventTypeID) WITH (FILLFACTOR = 90);
CREATE NONCLUSTERED INDEX Idx_History_CompanyID ON dbo.History (CompanyID) WITH (FILLFACTOR = 90);
CREATE NONCLUSTERED INDEX Idx_History_WorkerID ON dbo.History (WorkerID) WITH (FILLFACTOR = 90);
CREATE NONCLUSTERED INDEX Idx_History_EventTypeID ON dbo.History (EventTypeID) WITH (FILLFACTOR = 90);

This will contain a bunch of events, and you can just sub-class the events to your pleasing. Your code could, for example, look here when the associated EventDescription = 'Evaluated':

CREATE TABLE dbo.Evaluations (
      EvalID INT NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES dbo.History(HistID) ON UPDATE CASCADE ON DELETE CASCADE
    /* ...Eval columns... */
);

CREATE TABLE dbo.EvaluationItems (
      ID INT PRIMARY KEY IDENTITY(1,1)
    , EvalID INT NOT NULL FOREIGN KEY REFERENCES dbo.Evaluations (EvalID)
    /* ...item details... */
);
GO

This was provided just as a model, evaluation items don't have to be that way.

Next, the Most recent employers:

CREATE VIEW dbo.WorkersLastEmployer AS
    SELECT W.*, C.*
    FROM dbo.Workers W
        INNER JOIN (
            SELECT H.HistID, H.TS, H.CompanyID, H.WorkerID, MAX(TS)OVER(PARTITION BY H.WorkerID) AS LastHired
            FROM dbo.History H
                INNER JOIN dbo.EventTypes E ON E.EID = H.EventTypeID
            WHERE E.EventDescription = 'Hired'
        ) Hi ON Hi.WorkerID = W.WID
            AND Hi.TS = Hi.LastHired
        INNER JOIN dbo.Companies C on C.CID = Hi.CompanyID
GO

And since CompanyID is migrated to History, and Evaluations is now a direct subclass, this view for evaluations of worker X at company Y:

CREATE VIEW dbo.WorkerEvalutations AS
    SELECT E.*, W.*, C.*
    FROM dbo.Evaluations E
        INNER JOIN dbo.History H on E.EvalID = H.HistID
        INNER JOIN dbo.Workers W on W.WID = H.WorkerID
        INNER JOIN dbo.Companies C on C.CID = H.CompanyID
GO

This structure runs without warnings and will give you a more easily extensible framework.