CODEWITHSUNDEEP
sql-server
K.Z
K.Z

Reputation: 5075

Create one to one relationship in sql server management studio

I am trying to create one to one relationship between Branch to BranchEmployee and Employee to BranchEmployee. Whole idea is to separate employee data who is working in a branch. I am using SQL Server Management Studio but I am struggling with this. In BranchEmployee the BranchID and UserID combine together to be primary key for table.

Many Thanks

Screen shot is as below

enter image description here

Upvotes: 2

Views: 1954

Answers (2)

granadaCoder
granadaCoder

Reputation: 27852

More than likely, you actually have a 1:N relationship or a M:N relationship.

If a user can work for exactly ONE and ONLY ONLY Branch (1:N), then you can either put a BranchID(FK) in the Employee table, or use the 'extra' BranchEmployee(Link) table...provided you put a unique constraint on (BranchID, UserId) on the BranchEmployee(Link) table.

Most will prefer to put the BranchID(FK) in the Employee table, because that is less tables.

However, let's say you wanted to isolate that data, AND put some metadata about that relationship. Say "Employee Started At The Branch On This Date", aka EmployeeBranchStartDate.

Then the extra table makes a little more sense. Notice I said "a little".

BranchEmployee(Link) (Table)
-----------
BranchEmployeeLinkSurrogateKey
BranchId (FK)
UserId (FK)
EmployeeBranchStartDate (datetime)
(unique on BranchId, UserId)

Personally, I would go with the BranchEmployee(Link) now. Because if there is a pretty good chance that I might need an Employee to work at 2 branches (which would change the relationship to M:N), then I don't have to redesign everything. I can simply remove the (unique on BranchId, UserId) constraint. Aka, a little preventative design to save alot of heartache later.

Upvotes: 0

ApplePie
ApplePie

Reputation: 8942

Why even do this ? You can directly add the foreign key to the branch in the Employee table. This removes an extra table and makes your schema simpler. The only case I see that would make your design ok is if each employee either always moves from branch to branch or is attached to multiple branches but both scenarios seem unlikely, especially since you say you want to model a 1-1 relationship and not an N-N relationship.

Long story short, drop that BranchEmployees table.

Upvotes: 1

Related Questions