February 1, 2013 at 2:25 pm
I seem to be struggling with something that I thought was fairly straight-forward originally when I started designing the database layout. I'm unsure if the following database structure will work for mapping a many-to-many relationship between my "Employee" table and "DepartmentEmployee" table. Below is a simplified structure of the tables in question.
create table Company
(
CompanyID int,
CompanyName varchar(75) not null,
constraint pk_Company primary key (CompanyID)
)
create table Person
(
PersonID uniqueidentifier,
FirstName varchar(50) not null,
LastName varchar(50) not null,
constraint pk_Person primary key (PersonID)
)
create table Employee
(
CompanyID int not null,
PersonID uniqueidentifier not null,
EmployeeID int not null,
constraint pk_Employee primary key (CompanyID, PersonID),
constraint fk_Employee_Company foreign key (CompanyID) references Company (CompanyID),
constraint fk_Employee_Person foreign key (PersonID) references Person (PersonID)
)
create table Division
(
CompanyID int not null,
DivisionName varchar(50) not null,
constraint pk_Division primary key (CompanyID, DivisionName),
constraint fk_Division_Company foreign key (CompanyID) references Company (CompanyID)
)
create table Department
(
CompanyID int not null,
DivisionName varchar(50) not null,
DepartmentName varchar(50) not null,
constraint pk_Department primary key (CompanyID, DivisionName, DepartmentName),
constraint fk_Department_Division foreign key (CompanyID, DivisionName) references Division (CompanyID, DivisionName)
)
My goal is to be able to allow "Employee" have a many-to-many relationship with the "DepartmentEmployee" table. However, an employee may not always have a department and/or division provided. The following design for the table doesn't seem to make sense as the CompanyID is repeated and could allow for an employee of one company to be listed as an employee for another company:
create table DepartmentEmployee
(
DepartmentCompanyID int not null,
DivisionName varchar(50) not null,
DepartmentName varchar(50) not null,
EmployeeCompanyID int not null,
PersonID uniqueidentifier not null,
constraint pk_DepartmentEmployee primary key (DepartmentCompanyID, DivisionName, DepartmentName, EmployeeCompanyID, PersonID),
constraint fk_DepartmentEmployee_Department foreign key (DepartmentCompanyID, DivisionName, DepartmentName) references Department (CompanyID, DivisionName, DepartmentName),
constraint fk_DepartmentEmployee_Employee foreign key (EmployeeCompanyID, PersonID) references Employee (CompanyID, PersonID)
)
Another idea was to have the following
create table DepartmentEmployee
(
CompanyID int not null,
DivisionName varchar(50) not null,
DepartmentName varchar(50) not null,
PersonID uniqueidentifier not null,
constraint pk_DepartmentEmployee primary key (CompanyID, DivisionName, DepartmentName, PersonID),
constraint fk_DepartmentEmployee_Department foreign key (CompanyID, DivisionName, DepartmentName) references Department (CompanyID, DivisionName, DepartmentName),
constraint fk_DepartmentEmployee_Employee foreign key (CompanyID, PersonID) references Employee (CompanyID, PersonID)
)
Although the table is able to be created, this set-up doesn't seem correct to have a single column have a foreign key to both tables. Please let me know if you have any suggestions and/or alternatives to this design. Thank you very much!
February 1, 2013 at 3:02 pm
You have a lot of denormalized structures here.
Consider these two tables.
create table Division
(
CompanyID int not null,
DivisionName varchar(50) not null,
constraint pk_Division primary key (CompanyID, DivisionName),
constraint fk_Division_Company foreign key (CompanyID) references Company (CompanyID)
)
create table Department
(
CompanyID int not null,
DivisionName varchar(50) not null,
DepartmentName varchar(50) not null,
constraint pk_Department primary key (CompanyID, DivisionName, DepartmentName),
constraint fk_Department_Division foreign key (CompanyID, DivisionName) references Division (CompanyID, DivisionName)
)
Here you DivisionName in both the division AND the Department table. Does DivisionName belong to the department. No it belongs to Division. You also have CompanyID in both tables. There is a natural hierarchy here.
Company 1:* Division 1:* Department.
Meaning that any 1 company can have many Division and each Division can have many departments.
The way you have it, if you change the DivisionName in the Devision you have to change the Department table too.
create table Division
(
DivisionID int identity not null,
DivisionName varchar(50) not null,
CompanyID int not null,
constraint pk_Division primary key (DivisionID),
constraint fk_Division_Company foreign key (CompanyID) references Company (CompanyID)
)
create table Department
(
DepartmentID int identity not null,
DepartmentName varchar(50) not null,
DivisionID int not null,
constraint pk_Department primary key (DepartmentID),
constraint fk_Department_Division foreign key (DivisionID) references Division (DivisionID)
)
There are a number of other issues I see in your structures.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 1, 2013 at 3:09 pm
Also I would avoid using uniqueid's as your primary key unless there is a good reason. It is very wide for a key and they are a PITA to work with. I don't really see the need for the Person table.
Typically when you need to have a many:many relationship you use an intermediate table.
Here is how I would define the last 2 tables.
create table Employee
(
CompanyID int not null,
EmployeeID int identity not null,
constraint pk_Employee primary key (EmployeeID),
constraint fk_Employee_Company foreign key (CompanyID) references Company (CompanyID)
)
Create table DepartmentEmployee
(
DepartmentID int not null,
EmployeeID int not null,
constraint pk_DepartmentEmployee primary key (DepartmentID, EmployeeID)
constraint fk_DepartmentEmployee_DepartmentID foreign key (DepartmentID) references Department (DepartmentID),
constraint fk_DepartmentEmployee_EmployeeID foreign key (EmployeeID) references Employee (EmployeeID)
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 3, 2013 at 3:47 pm
Thank you for your reply Sean. I apologize for the delay in responding as I've been out of town over the weekend.
In your first reply you note that there is a natural hierarchy between Company, Division, and Department and that I would need to change the DivisionName in the Department table if the DivisionName changes in the Division table. In my haste to get the question posted on the forum before leaving the office I failed to put a Cascade Update/Delete rules on the foreign key constraints. If there's a cascade rule on the "fk_Department_Division" constraint, the an update to the DivisionName would be reflected in the Department. Would you recommend adding an identity column and using that instead of a Cascade rule?
As for the uniqueidentifier usage in the Person table, unfortunately I cannot rely on items such as SSN and/or EmployeeID numbers as the system will be receiving information from a variety of external sources. Each source may have their own issues with providing reliable data (e.g. no ssn, duplicate employee IDs, no employee IDs). Thus, the only solution to this would be an identity column and/or uniqueidentifier as no there's no viable option for a primary key otherwise. I understand that the uniqueidentifier type is very wide to work with, however, I plan to utilize other columns such as first/last name for the clustered index instead of the uniqueidentifier. Please let me know if you have other thoughts and/or a better idea on how to do this.
Going back to my original question regarding the best way to implement the DepartmentEmployee bridge table for the many-to-many relationship. Based on your responses, it looks like adding a new column for the primary key (instead of using a composite key) may be my best choice. I appreciate the feedback you've given. Have a great night and enjoy the Superbowl as I know I'll be watching in yearning for the day that the Detroit Lions play in it. 🙂
February 4, 2013 at 7:51 am
Why use cascading updates to keep denormalized data in synch? Just normalize it and you don't have to worry about it. Keep the data in one and only one table.
As for sticking with a GUID as a primary key, you will regret that at some point. I understand about receiving data from a number of external sources. Use an identity or you will have fragmented indexes that you will constantly be fighting. Here is an article discussing index fragmentation on a non-clustered index using guids.
http://www.sqlskills.com/blogs/paul/can-guid-cluster-keys-cause-non-clustered-index-fragmentation/[/url]
There are mountains of other articles about not using a guid as an index. Why not just use an identity? It is far simpler to work with and if you really need to support a large number of rows use BIGINT.
I don't know that I would use FirstName and/or LastName as a clustered index unless you plan on querying the table with the name in the where clause a lot.
Hope you enjoyed the game, maybe someday your Lions will make it. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2013 at 9:17 am
NuNn DaddY (2/1/2013)
The following design for the table doesn't seem to make sense as the CompanyID is repeated and could allow for an employee of one company to be listed as an employee for another company
Actually, that is very possible, but, depending on your particular business rules, you may (or may not) want to allow (or disallow) that particular scenario. Not knowing what you're building this database for, consider someone who moonlights, has a private business in addition to their "day job", or delivers pizzas.
I'm a Business Analyst, so the types questions I always ask are the "What happens if..." kind:
What happens if the Person is also a Customer? Have you accounted for that?
What happens if the Employee is dotted-lined to multiple VP's in multiple departments?
What happens if the Department is swallowed up by another Division? Do you need to keep the "history" that Department A used to be in Division B for accounting purposes?
Etc.
(And I'm cheering for the Chiefs to get back to the SB someday!)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply