This is a question for an SQL expert. I am using SQL Server 2008 R2
I have two corresponding tables: Labs
and LabUsers
.
Users are assigned Labs without repeating entire groups of any order.
The goal is to insert @userName
(for example, @user = "Paul"
) in LabUsers
, fulfilling all of the following restrictions:
In group no more than @maxUsers
(for example @maxUsers=4
)
No duplicates of complete groups (full laboratories). The order of users in the group is negligible. [edit]
If no existing Lab is allowed, create ( INSERT
) a new lab, then insert the line for @user
if @user
is not specified (for example @maxLabs=5
).
Very important . There are many parallel identical requests from the server in a split second, which can interfere with each other. Therefore, as soon as a command begins to be executed, no other requests can be executed until the end of this command.
The query should return 0 in cases where it cannot fulfill the above restrictions, and returns the LabID
inserted row.
[EDITED] There are several zones labs. The zones are independent. Each #labCount zone is limited to @maxLabs
. The value of @maxLabs
is equal for all zones, therefore Total_maxLabs
= @maxLabs
x #zonesCount
. For example, @zone=51
(later @zone=52, 53 etc.
). (The same LabUsers can use zones without restrictions. Zones do not know about each other)
LabID
in LabUsers
is a foreign key from Labs
.
Example:
Here is the Labs
table:
LabID LabName LabZone ----- ------- ------- 1 North 51 2 North East 51 3 South West 51
And LabUsers
:
LabUserID LabUserName LabID --------- ----------- ----- 1 Diana 3 2 Julia 2 3 Paula 2 4 Romeo 1 5 Julia 3 6 Rose 2 7 Diana 1 8 Diana 2 9 Julia 1 10 Romeo 3 11 Paul 1
In the example, users are assigned as follows:
LabID LabName LabZone LabUsers (ordered LTR a>z) ----- ------- ------- -------- 1 North 51 Diana•Julia•Paul•Romeo 2 North East 51 Diana•Julia•Paula•Rose 3 South West 51 Diana•Julia•Romeo
- The insert should not be in
LabID=1
or 2, because there are already 4 users in these labs. - The insertion should not take place in
LabID=3
due to the creation of a duplicate with LabID=1
.
Therefore, since @maxLabs
not 3 (existing laboratories), you must insert a new line in Labs
with the value LabZone=@zone =51
.
IDENTITY
sets LabID
to 4 for a new line.
It's time to insert Paul
into LabUsers
with the LabID
just returned from the new lab insert.
How to solve this problem?
What is the method used to ensure that the command is executed as a whole without interference?
script to create the database:
CREATE DATABASE [Allocation] GO USE [Allocation] GO CREATE TABLE [dbo].[LabUsers]( [LabUserID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED , [LabUserName] [nvarchar](50) NOT NULL, [LabID] [int] NOT NULL) GO SET IDENTITY_INSERT [dbo].[LabUsers] ON INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (1, N'Diana', 3) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (2, N'Julia', 2) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (3, N'Paula', 2) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (4, N'Romeo', 1) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (5, N'Julia', 3) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (6, N'Rose', 2) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (7, N'Diana', 1) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (8, N'Diana', 2) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (9, N'Julia', 1) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (10, N'Romeo', 3) INSERT [dbo].[LabUsers] ([LabUserID], [LabUserName], [LabID]) VALUES (11, N'Paul', 1) SET IDENTITY_INSERT [dbo].[LabUsers] OFF CREATE TABLE [dbo].[Labs]( [LabID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED , [LabName] [nvarchar](50) NULL, [LabZone] [int] NOT NULL) GO SET IDENTITY_INSERT [dbo].[Labs] ON INSERT [dbo].[Labs] ([LabID], [LabName], [LabZone]) VALUES (1, N'North', 51) INSERT [dbo].[Labs] ([LabID], [LabName], [LabZone]) VALUES (2, N'North East', 51) INSERT [dbo].[Labs] ([LabID], [LabName], [LabZone]) VALUES (3, N'South West', 51) SET IDENTITY_INSERT [dbo].[Labs] OFF