There is the following schema DB:

enter image description here

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 ?

  • 3
    Create a separate table. - Sergey
  • one
    "add a field like varchar in which to store performers Id through a separator". Normalization? No, not heard =) create a table of performers and a many-to-many connection - Dmitry Chistik
  • @DmitryChistik heard, if I hadn’t heard, there was probably no second option - Bald
  • Well, then the question itself actually decides. You would not know that storing ideers in a row is not an option. - Dmitry Chistik
  • Well, of course, there is a separate table, just think about whether an Id needed 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

0