0

My db hierarchy is like Group -> Branch -> department. Employee and Roles are different table ,tbl_Employee having FK groupid,branchid, departmentid and RoleId. If I assign an employee as a '1' which is a groupHead according to Roles table then I have to provide this employee's branchId and depId as well. But all Branches of the particular group comes under him . So I was thinking of writing all the branchIds which comes under him. How should I proceed ?

Improve this question
7
  • I don't understand this question. What does the title mean? Do you mean one foreign key reference that references two different tables? Or do you mean you want to store more than one value into one column? And how does the content of this question relate to the title? Commented Apr 25, 2014 at 8:43
  • @LasseV.Karlsen Please read it once more. Its correct. Commented Apr 25, 2014 at 8:45
  • Why not model it as an Employee with null departmentID is attached to the branch, and an Employee with null branchID and departmentID is attached to the group, and for each, it's automatically implied that they have all items below that? You could even have a check constraint that ensures e.g. that they only have NULLs in particular columns where the RoleID is correct. Commented Apr 25, 2014 at 8:45
  • @Damien_The_Unbeliever- But how can I pass NULL values in FK fields which are in fact primary keys of other table and Primary keys cannot be NULL. Commented Apr 25, 2014 at 8:48
  • @LasseV.Karlsen Yes I mean more than one value into one column. What does the foreign key column holds ? Primary keys of other table. Commented Apr 25, 2014 at 8:54

1 Answer 1

Reset to default
1

You can build a structure like this:

create table Groups ( GroupID char(2) not null primary key ) create table Branches ( GroupID char(2) not null foreign key references Groups (GroupID), BranchID char(2) not null primary key, constraint UQ_Branch_XRef UNIQUE (GroupID,BranchID) ) create table Departments ( GroupID char(2) not null foreign key references Groups (GroupID), BranchID char(2) not null foreign key references Branches (BranchID), DepartmentID char(2) not null primary key, constraint UQ_Department_XRef UNIQUE (GroupID,BranchID,DepartmentID), constraint FK_Department_Branch_XRef FOREIGN KEY (GroupID,BranchID) references Branches (GroupID,BranchID) ) create table Employees ( EmployeeID char(2) not null primary key, GroupID char(2) not null references Groups (GroupID), BranchID char(2) null references Branches (BranchID), DepartmentID char(2) null references Departments (DepartmentID), RoleID int not null, constraint FK_Employee_Branch_XRef FOREIGN KEY (GroupID,BranchID) references Branches (GroupID,BranchID), constraint FK_Employee_Department_XRef FOREIGN KEY (GroupID,BranchID, DepartmentID) references Departments (GroupID,BranchID,DepartmentID), constraint CK_AppropriateRole CHECK ( (RoleID = 1 and BranchID is null and DepartmentID is null) or (RoleID = 2 and BranchID is not null and DepartmentID is null) or (RoleID = 3 and BranchID is not null and DepartmentID is not null) ) )

And then populate it like this:

insert into Groups (GroupID) values ('aa'),('bb') insert into Branches (GroupID,BranchID) values ('aa','cc'),('aa','dd'),('bb','ee'),('bb','ff') insert into Departments (GroupID,BranchID,DepartmentID) values ('aa','cc','gg'), ('aa','cc','hh'), ('aa','dd','ii'), ('bb','ee','jj'), ('bb','ff','kk'), ('bb','ff','ll')

And insert some valid employees

insert into Employees (EmployeeID,GroupID,BranchID,DepartmentID,RoleID) values ('mm','aa',null,null,1), ('nn','aa','dd',null,2), ('oo','bb','ee','jj',3)

But then you can't insert invalid rows:

insert into Employees (EmployeeID,GroupID,BranchID,DepartmentID,RoleID) values ('pp','bb',null,null,3) insert into Employees (EmployeeID,GroupID,BranchID,DepartmentID,RoleID) values ('qq','aa','cc','hh',1) insert into Employees (EmployeeID,GroupID,BranchID,DepartmentID,RoleID) values ('rr','aa','cc','ii',3)

And then it's implied that 'mm' is attached to group 'aa' and all branches and departments under it, 'nn' is attached to branch 'dd' of group 'aa' and all departments under that, and 'oo' is in department 'jj' of branch 'ee' of group 'bb'.

Whenever you store a NULL in (a column of) a foreign key, in SQL Server, then SQL Server doesn't enforce that foreign key.

The Constraints

There are 16 constraints in the 4 above tables, and most of them serve an important purpose. I'll ignore the primary keys since they're just fulfilling their usual role.

create table Branches ( GroupID char(2) not null foreign key references Groups (GroupID),

Just a plain foreign key. Nothing special yet

 ... constraint UQ_Branch_XRef UNIQUE (GroupID,BranchID)

We need this unique constraint so that foreign keys can reference this table by both columns, so that we can cross-check that where a BranchID is used, it's used with the correct GroupID.

) create table Departments ( GroupID char(2) not null foreign key references Groups (GroupID), BranchID char(2) not null foreign key references Branches (BranchID),

The above two foreign key references aren't strictly necessary. The other FK defined below on this table will also ensure that the values are valid. But they are the "natural" FKs that most people would define. Whether you keep them or not is a matter of preference and possibly performance.

 ... constraint UQ_Department_XRef UNIQUE (GroupID,BranchID,DepartmentID),

This fulfils a similar role to UQ_Branch_XRef above, allowing FKs to reference this table by all 3 columns to ensure that all of the values are consistent.

 constraint FK_Department_Branch_XRef FOREIGN KEY (GroupID,BranchID) references Branches (GroupID,BranchID)

And this is the first FK that makes use of UQ_Branch_XRef, ensuring that our GroupID column is appropriate for our chosen BranchID.

) create table Employees ( ... GroupID char(2) not null references Groups (GroupID),

This FK constraint is important if both BranchID and DepartmentID are null - it's our only FK constraint that actually applies in such a circumstance.

 BranchID char(2) null references Branches (BranchID), DepartmentID char(2) null references Departments (DepartmentID),

These 2 FKs are technically redundant, given other FKs declared below, but they're the "natural" FKs.

 ... constraint FK_Employee_Branch_XRef FOREIGN KEY (GroupID,BranchID) references Branches (GroupID,BranchID),

This ensures that our GroupID and BranchID are correct. This FK is only important if DepartmentID is null, since in such a circumstance, the next FK won't apply a constraint

 constraint FK_Employee_Department_XRef FOREIGN KEY (GroupID,BranchID, DepartmentID) references Departments (GroupID,BranchID,DepartmentID),

This is the constraint that ensures all 3 of the named columns match up with the entry in the Departments table.

 constraint CK_AppropriateRole CHECK ( (RoleID = 1 and BranchID is null and DepartmentID is null) or (RoleID = 2 and BranchID is not null and DepartmentID is null) or (RoleID = 3 and BranchID is not null and DepartmentID is not null) )

And finally this is the constraint that applies some business rules about what roles should have which columns filled in.

)
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

I am really thankful to you. My db structure is similar but I needed to add the CHECK constraint. New thing I learnt today. Thanks again.
Can you tell me one thing that why have you taken two constraints ? We could write them in one constraint as well or not ? Please throw some light.
Which two constraints are you talking about? My example contains 16 constraints spread across 4 tables
constraint FK_Employee_Branch_XRef and constraint FK_Employee_Department_XRef
Ok. Consider a role 2 employee. You've put certain values in their GroupID and BranchID columns and left DepartmentID NULL. The (implicit) foreign keys on the GroupID and BranchID columns are the "natural" foreign keys back to their respective tables, but aren't actually required. FK_Employee_Branch_XRef is a foreign key back to the Branches table that not only ensures that we're referencing a row in Branches, but it also ensures that that branch has the same GroupID. The department one operates similarly when all 3 columns aren't NULL.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.