Making sure that Employees don't work in more than max. 2 projects at the same time

Question

Sorry for asking such a dumb question. I'm pretty new to SQL in general.

The question is: I have 2 tables

CREATE TABLE [dbo].[Projects] 
(
    [name]       VARCHAR (30)  NOT NULL,
    [company]    VARCHAR (200) NOT NULL,
    [manager]    VARCHAR (30)  NOT NULL,
    [start_date] DATETIME      NOT NULL,
    [end_date]   DATETIME      NULL,

    PRIMARY KEY CLUSTERED ([name] ASC, [company] ASC),

    FOREIGN KEY ([company]) 
        REFERENCES [dbo].[Companies] ([email]) 
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY ([manager]) 
        REFERENCES [dbo].[Managers] ([username]) 
);

CREATE TABLE [dbo].[Manager_assign_Regular_Employee_Project] 
(
    [project_name]     VARCHAR (30)  NOT NULL,
    [company]          VARCHAR (200) NOT NULL,
    [regular_employee] VARCHAR (30)  NOT NULL,
    [manager]          VARCHAR (30)  NOT NULL,

    PRIMARY KEY CLUSTERED ([project_name] ASC, [company] ASC, [regular_employee] ASC),

    FOREIGN KEY ([project_name], [company]) 
        REFERENCES [dbo].[Projects] ([name], [company]) 
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY ([regular_employee]) 
        REFERENCES [dbo].[Regular_Employees] ([username]),
    FOREIGN KEY ([manager]) 
        REFERENCES [dbo].[Managers] ([username]) 
);

I want to make sure that one employee doesn't work in more than 2 projects at the same time.

For example :

p1 regular1 STARTDATE=1/1/2017 ENDDATE=1/5/2017
p2 regular1 STARTDATE 1/5/2017 ENDDATE=1/20/2017

These two records are okay to have, but if I add another that intersect with the 2 i.e 3 projects at the same it should be rejected

I tried to do a join between 2 project table and the second one but I failed horribly

What did you try, and what result did you get? What result are you expecting? Is your code MySQL, or perhaps MS-SQL? If your question relates to SQL Server, you may need to tag this differently. (edit: looks like Ramond has helped you with the tags. Thanks.) — Brian Dewhirst, Nov 22 '17 at 16:20
i've changed the tag from MySQL to SQL-server. because this is clearly SQL-server syntax. — Raymond Nijland, Nov 22 '17 at 16:22
i tried to join 2 projects table with Manager_assign_Regular_Employee_Project table and check if p1 overlaps the date of p2 and count when this happens — Mohamed Hesham Ibrahim, Nov 22 '17 at 16:23
i geuss you need to create a trigger that rejects the insert when the employee matches the date interval.. — Raymond Nijland, Nov 22 '17 at 16:27
@MohamedHeshamIbrahim Do you enjoy asking/responding to a thousand questions? Why can't you use triggers? At some point, you must write code to either enforce your requirement at insert/update time or you write logic that will do the same thing **prior** to insert/update. You choose. — SMor, Nov 22 '17 at 16:35
@SMor well , i can't use something i didn't take in my course yet , i really want to but i can'y — Mohamed Hesham Ibrahim, Nov 22 '17 at 16:47
Possible duplicate of [Date range overlapping check constraint](https://stackoverflow.com/questions/12035747/date-range-overlapping-check-constraint) — underscore_d, Nov 22 '17 at 16:54
"i can't use something i didn't take in my course yet ". Why not? You don't have to wait to be spoon-fed. Research and implement it yourself. The internet is a mine of information. Your course tutor ought to give you extra marks for initiative. — ADyson, Nov 22 '17 at 21:28
Not sure how else a record will be rejected on insert except from a trigger. — clinomaniac, Nov 22 '17 at 22:44

score 1 · Answer 1 · answered Nov 23 '17 at 00:32

At some point you need to figure out what you should do before you start writing code. You said "I want to make sure ...". So describe your process. Asking others for suggestions isn't useful when they have no idea what exactly you are trying to accomplish.

Since this is a class, you should be trying to figure this out yourself. Given 2 assignment rows, how do you know if they overlap? Sometimes it helps to actually draw things on paper. Draw a timeline that encompasses a span of time. Say January 1 through June 30. Now draw a line above this timeline that represents a random period of time. Say Feb 5 to Mar 15. Next draw a range of periods that overlap and do not overlap your first period. Now look each of the "proposed" periods (2 through 5) and determine what logic you need to determine the logic needed to find those that overlap and ignore those that do not. An example of such a drawing is

A hint - you compare start to end and end to start. Ignore null values for now to make things a bit easier. Another hint - put these rows in your table and write a query to find which rows overlap row 1.

And one more comment. No - your sample data is NOT ok. The end date is the last day that an employee works on a project. So that same employee cannot also start work on a different project. That is overlap. Your dates must be considered inclusive. Otherwise, there is a world of problems you will face. And if you are only concerned about dates, then DON'T USE DATETIME columns. Think about the correct datatypes to use for your columns. Don't just slap something together out of habit or laziness. There are other problems with the schema, but focus on one thing at a time.

Making sure that Employees don't work in more than max. 2 projects at the same time

1 Answers1