February 12, 2013 at 8:24 am
I need to create a simple employee SQl database. One of the requirements is to create primary and foreign keys in the tables. I'm a little confused on creating the foreign keys though. I have listed an example below. Is it acceptable to add the Emp_Id column to each table and use that as my foreign key. Each record will be linked to an employee in the Emp_Name table. Any advice or other recomendations would be helpful. Thanks in advance.
Emp_Name_tbl
Emp_Id - Primary Key
Emp_First_Name
Emp_Last_Name
Emp_MI
Employee_Org_tbl
Emp_Org_Id - Primary Key
Emp_Div_Name
Emp_Dept
Emp_Id
Emp_Contact_tbl
Emp_Contact_Id - Primary Key
Emp_Home_Phone
Emp_Mobile_Phone
Emp_Work_Phone
Emp_Id
February 12, 2013 at 8:46 am
Yes, I would make the Emp_Id column on the other tables the foreign key. They link back to the Primary Key of Emp_Id on the Emp_Name table.
nsmith 8448 (2/12/2013)
I need to create a simple employee SQl database. One of the requirements is to create primary and foreign keys in the tables. I'm a little confused on creating the foreign keys though. I have listed an example below. Is it acceptable to add the Emp_Id column to each table and use that as my foreign key. Each record will be linked to an employee in the Emp_Name table. Any advice or other recomendations would be helpful. Thanks in advance.Emp_Name_tbl
Emp_Id - Primary Key
Emp_First_Name
Emp_Last_Name
Emp_MI
Employee_Org_tbl
Emp_Org_Id - Primary Key
Emp_Div_Name
Emp_Dept
Emp_Id
Emp_Contact_tbl
Emp_Contact_Id - Primary Key
Emp_Home_Phone
Emp_Mobile_Phone
Emp_Work_Phone
Emp_Id
February 12, 2013 at 8:47 am
Yes, that's how foreign keys work. You have a foreign key in a child table that references that references a primary key in the parent table.
If you're creating an organizational structure with, say, departments, you should think about the overall table structure. I would create an Employees table and also a Departments table. Then have a DepartmentID field in the Employees table that's a foreign key to Departments.ID. This assumes that an employee can belong to only one department at a time. Example:
create table Departments (
ID integer not null identity (1, 1),
constraint Departments_PK primary key (ID),
Department varchar(32));
create table Employees (
ID integer not null identity (1, 1),
constraint Employees_PK primary key (ID),
FirstName varchar(32),
LastName varchar(32),
DepartmentID integer not null,
constraint EmployeeDepartments_FK
foreign key (DepartmentID)
references Departments (ID));
If an employee can belong to more than one department at a time, you'll instead want to eliminate the foreign key from Employees and create what's called a resolve table between Employees and Departments that contains foreign keys to both tables. Example:
create table Employees (
ID integer not null identity (1, 1),
constraint Employees_PK primary key (ID),
FirstName varchar(32),
LastName varchar(32));
create table Departments (
ID integer not null identity (1, 1),
constraint Departments_PK primary key (ID),
Department varchar(32));
create table EmployeeDepartments (
ID integer not null identity (1, 1),
constraint EmployeeDepartments_PK primary key (ID),
EmployeeID integer not null,
constraint EmployeeDepartments_Employees_FK
foreign key (EmployeeID)
references Employees (ID),
DepartmentID integer not null,
constraint EmployeeDepartments_Departments_FK
foreign key (DepartmentID)
references Departments (ID));
--to make sure an employee doesn't belong to the same department more than once
create unique nonclustered index EmployeeDepartments_UQ on EmployeeDepartments(EmployeeID, DepartmentID);
Of course, these table definitions aren't complete, but just serve as an example of how to relate tables to one another. The primary key on EmployeeDepartments is open to debate; you could also go with a composite primary key consisting of both EmployeeID and DepartmentID, but that's a matter of personal preference.
HTH.
February 12, 2013 at 10:16 am
nsmith 8448 (2/12/2013)
One of the requirements is to create primary and foreign keys in the tables.
Is it acceptable to add the Emp_Id column to each table and use that as my foreign key[?]
Any advice or other recomendations would be helpful.
So this is some type of class assignment?
At any rate, yes, it is definitely acceptable to use the Emp_Id column as a FK from other tables.
This design is physical, not logical, so I will address proper clustering as well.
Emp_Name_tbl --<<-- the "_tbl" suffix is obsolete and should not be used
Emp_Id - Primary Key --<<-- OK; should also be the clustering key on this table
Employee_Org_tbl
Emp_Org_Id - Primary Key --<<-- OK
Emp_Id --<<-- Is a FK back to the Emp_Name_tbl
Emp_Contact_tbl
Emp_Contact_Id - Primary Key --<<-- OK if you really want it, but should NOT be the clustering key
Emp_Id --<<-- should be the clustering key for this table; is a FK back to Emp_Name table
[Contact_Sequence] --<<-- optional, remove if not needed: seq# for contact: 1=try first; 2=try next; etc.
Contact_Location --<<-- alpha or numeric code for contact location: Home|Alternate Home|Work|etc..
Contact_Type --<<-- alpha or numeric code for contact type: Phone|Email|etc..
Contact_Data --<<-- phone#|email address|...
{ --<<-- Wrong! I removed this! It's denormalized, i.e. bad design.
--There should be one contact per row.
Emp_Home_Phone
Emp_Mobile_Phone
Emp_Work_Phone
}
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 13, 2013 at 12:55 pm
Thanks everyone. Great input. No school project, just thrown into the fire without much experience in creating databases. Also, not using "tbl" on the tables, just noted it in the thread hoping to prevent confusion. The removal of the renormalized tables makes much more sense and helped clarify some of my own confusion.
Based on the comments above, when using Emp_Id (Which is always unique) in other tables can I make it my primary key and foreign key (when needed)?
If so, would there be any reason to keep the specific table Id columns (Ex: Emp_Contact_Id) ? I would rather not have them unless there is a reason to keep them.
Also, When creating the primary key the clustered index\key was created automatically if one hasn’t been specified. Is this correct or should I define these manually?
Thanks in advance for the help.
February 13, 2013 at 1:12 pm
Hmm, if you're going to go live with this, you need to do some further normalization and use less-prefixed names, as below.
Employee (abbreviated "Emp" below): Clus_Key = ( Emp_Id )
....Emp_Id PK --could be IDENTITY column or be provided from another source, such as HR
....First_Name
....Last_Name
....Middle_Name
Employee_Organization: Clus_Key = ( Dept_Id, Emp_Id ) <OR> ( Emp_Id )
--IF an emp can be in only one deparment:
....Dept_Id FK --> Deparment table, which has FK of Div_Id to a Division table
....Emp_Id FK
--ELSE if an emp can be in more than one deparment:
....Emp_Org_Id --IDENTITY, PK
....Dept_Id FK --> Deparment table, which has FK of Div_Id to a Division table
....Emp_Id FK
(tbc...)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 13, 2013 at 1:21 pm
Thanks SP. I kinda understand what you posted but a bit lost. Can you clarify this based on my questions above?
February 13, 2013 at 1:23 pm
Employee_Contact: Clus_Key = ( Emp_Id, Location, Type[, Sequence] )
....Emp_Id FK
....Location FK --<<-- alpha or numeric code for contact location: Home|Work|etc..
....Type FK --<<-- alpha or numeric code for contact type: Phone|Email|etc..
....Sequence --<<-- optional, if multiple values are to be allowed for the same location and type
....Data --<<-- phone#|email address|... :: should be validated when inserted or updated
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 13, 2013 at 1:30 pm
nsmith 8448 (2/13/2013)
Thanks everyone. Great input. No school project, just thrown into the fire without much experience in creating databases. Also, not using "tbl" on the tables, just noted it in the thread hoping to prevent confusion. The removal of the renormalized tables makes much more sense and helped clarify some of my own confusion.Based on the comments above:
(1) when using Emp_Id (Which is always unique) in other tables can I make it my primary key and foreign key (when needed)?
(2) If so, would there be any reason to keep the specific table Id columns (Ex: Emp_Contact_Id) ? I would rather not have them unless there is a reason to keep them.
(3) Also, When creating the primary key the clustered index\key was created automatically if one hasn’t been specified. Is this correct or should I define these manually?
Thanks in advance for the help.
1) Yes, you should use Emp_Id as either a PK or FK whenever it matches the needs of the table it's in.
2) That's a tricky q, and you will get different answers. I suggest keeping the identity columns but NOT making them the clustered index, or even the PK unless you have nothing else to be the PK.
3) NO, it's NEVER correct to allow a clustered index to be created by default/accident. You should explicitly create the desired clustered index on each table yourself.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply