Junction Table in SQL Server

Question

I am having difficulty creating a junction table in a new database. I used these queries to create the tables:

CREATE TABLE dbo.EmployeeUnavailability 
(
 ID INT IDENTITY(1,1) PRIMARY KEY,
 EmployeeName varchar(50),
 Monday varchar(10),
 Tuesday varchar(10),
 Wednesday varchar(10),
 Thursday varchar(10),
 Friday varchar(10),
 Saturday varchar(10),
 Sunday varchar(10),
 SupervisorName varchar(50),
)
GO 

CREATE TABLE dbo.EmployeeAttendance
(
 ID INT IDENTITY(1,1) PRIMARY KEY,
 EmployeeName varchar(50),
 Monday varchar(20),
 Tuesday varchar(20),
 Wednesday varchar(20),
 Thursday varchar(20),
 Friday varchar(20),
 Saturday varchar(20),
 Sunday varchar(20),
 SupervisorName varchar(50),
)
GO 

CREATE TABLE dbo.EmployeePerformance 
(
 ID INT IDENTITY(1,1) PRIMARY KEY,
 EmployeeName varchar(50),
 Attitude varchar(250),
 AttitudeDD tinyint,
 WorkQuality varchar(250),
 WorkQualityDD tinyint,
 Communication varchar(250),
 CommunicationDD tinyint,
 Leadership varchar(250),
 LeadershipDD tinyint,
 ImprovementAchievement varchar(250),
 ImprovementAchievementDD tinyint,
 SupervisorName varchar(50),
)
GO

From there, I am having difficulty creating a junction table that will reference all three tables. The employee names and supervisor names will be the same across the three tables.

Employee is an entity in your domain. Therefore you should have an Employee table for sure. In that case the Name of the employee will be in Employee table and you wouldn't have to repeat it multiple times. Take a look at dbo.EmployeeUnavailability. What a row in that table is telling ? What is the meaning of column Monday ? OK, what week we are talking about ? Rename your columns to give more clarity. — pero, Apr 01 '13 at 20:41
Sorry, you need to go back to basics. You do not have a column for each day of the week. — Kermit, Apr 01 '13 at 20:49
One can have such columns if we add a year information and a week number in that year. That way we would be able to precisely identify the dates we are capturing info. But I wouldn't suggest that design. It's unnecessarily complicated. — pero, Apr 01 '13 at 20:55

score 1 · Answer 1 · answered Apr 01 '13 at 20:49

Having a table referencing all 3 tables wouldn't be a good idea IMO. It would allow that a row in that junction table can reference rows in other tables that have nothing in common. I.e. a row in junction table could reference a row for John in 1st, Mary in 2nd and Garry in 3rd table.

Make a Employee table with only attributes from Employee entity. Add EmployeeUnavailability that will have it's ID, point to a row in Employee and identify the dates with a column of type datetime.

Employee attendance has nothing to do with Employee performance. One employee can show rarely but be a performance ninja.

Junction Table in SQL Server

1 Answers1