There is the following schema DB:
Where:
- Requests - Applications;
- RequestHistories - application life history;
- RequestStates - directory of application states ( Open , In Work , Closed , etc.);
- RequestTypes - directory of application types ( Emergency , Planned , etc.);
It is necessary to add the ability to specify performers for the application stage , i.e. For each record in the RequestHistories table RequestHistories can be any number of performers .
The Performers table is planned to look something like this:
CREATE TABLE dbo.NewPerformers ( Id INT IDENTITY, Surname NVARCHAR(100) COLLATE Cyrillic_General_CI_AS NOT NULL, Name NVARCHAR(50) COLLATE Cyrillic_General_CI_AS NOT NULL, Patronymic NVARCHAR(50) COLLATE Cyrillic_General_CI_AS NOT NULL, UnitId INT NOT NULL, CONSTRAINT [PK_dbo.NewPerformers] PRIMARY KEY CLUSTERED (Id), CONSTRAINT [FK_dbo.NewPerformers_dbo.Units_UnitId] FOREIGN KEY (UnitId) REFERENCES dbo.Units (Id) ON DELETE CASCADE ) ON [PRIMARY] GO Several options come to my mind: add a varchar field in the RequestHistory table in which to store the performers Id through the separator, or you can create a separate RequestHistoryPerformers table:
CREATE TABLE dbo.RequestHistoryPerformers ( Id INT IDENTITY, RequestHistoryId INT NOT NULL, PerformerId INT NOT NULL, CONSTRAINT [PK_dbo.RequestHistoryPerformers] PRIMARY KEY CLUSTERED (Id), CONSTRAINT [FK_dbo.RequestHistoryPerformers_dbo.Performers_PerformerId] FOREIGN KEY (PerformerId) REFERENCES dbo.Performers (Id) ON DELETE CASCADE, CONSTRAINT FK_RequestHistoryPerformers_RequestHistories_Id FOREIGN KEY (RequestHistoryId) REFERENCES dbo.RequestHistories (Id) ) ON [PRIMARY] GO Actually the question is how best / correctly to organize the storage of artists for the application stage ?

Idneeded in it, or a composite primary key will be enough(RequestHistoryId, PerformerId), which in addition will ensure the uniqueness of the pair, which, by the way, you do not have now. - i-one