CODEWITHSUNDEEP
sql-serverdatabase-designnormalizationrdbmserd
user1263981
user1263981

Reputation: 3147

Entity relation

Is the following entity relation correct? I am trying to link employee, timesheet and approver table.

There are few other tables and fields but for this question my main concern is following three tables. One employee can have more than timesheet approver.

Employee
--------
EmpID pk
Name

TimeSheet
------------
TSHEET_ID PK
FK_EmpID FK
Approved_By

Timesheet_Approver (one employee can have more than one approver
                    but only one will be approving the timesheet)
------------------
EmpID
Approver_EmpID

Employee Table Data:

EmpID Name
----- -----
1     john
2     david
3     mark

Timesheet Approver Data:

EmpID Approver
----- --------
1     2
1     3
2     3

Timesheet 

TSID EMPID APPROVED_BY
---- ----- -----------
101  1     2
102  1     3
103  2     3



OR this looks okay?

enter image description here

Upvotes: 0

Views: 253

Answers (4)

Pankaj
Pankaj

Reputation: 10095

My Suggestion

Why do you want Approver as a seperate table? Can you keep a Bit Field like IsApprover in Employee table? Otherwise you are creating redundancy

Upvotes: 1

9T9
9T9

Reputation: 698

Employee

EmpID - Pk

Name

TimeSheet

TSHEET_ID - PK

EmpID - FK

ApproverID - FK

Approvers

ApproverID -PK

Department

OK this is my final answer. If it doesn't suit you can try your own one. I don't have time to read all those comments. And I was trying to help you. Using the Approvers relation you can get approvers in a department and other things. Check sql queries to get those data.

Upvotes: 0

9T9
9T9

Reputation: 698

If there are several approves we can't add a column called Approved_by to the TimeSheet table. Then we can add two more relations. One is for approvers list.

Approvers

ApproverID

Department

Name

TimeSheetApproves

TimeSheetID

Department1ApproverID

Department2ApproverID

..........................so on

Don't forgot to remove Approved_by to the TimeSheet realtion.

Upvotes: 0

9T9
9T9

Reputation: 698

I feel something wrong in the design. This is my idea.

Employee

EmpID pk

Name

TimeSheet

TSHEET_ID PK

FK_EmpID

Approved_By

Third table is unnecessary.

Upvotes: 0

Related Questions