Question :-

Suppose we have to design a database in which employees can work on multiple projects.There are many designation defined in the organization to which each employee can belongs.
 
For example, Software engineer, Team lead, Project Manager, Project lead, QC engineer, QC Lead, QC manager etc. An employee can belong to one designation
at a time.  It means at a time an employee can either be Software engineer or team lead or project manager but not software engineer as well team lead at a same time.

Also the projects can work on different technologies like .Net, SQL Server, Oracle, HTML5 etc. Every project has a start data and end date. A project can work can include many technologies like .net, SQL Server , Java script, HTML5 etc. Most of the time every project work include a front end server technology like JAVA. Asp.net, uses Java script, HTML, CSS as client side technologies and SQL Server, oracle as back end database server. So In this ways a project work can include many technologies as in above example,  suppose a project can use Asp.net as server end front end technology, java script, HTML, CSS , AJAX as client side technology and SQL server as back end database server.

What should be the design the database schema (tables, their foreign keys, primary keys etc.

Answer:-

In this Question, main challenge is to how we can identified the main objects whose information is stored in master tables and how we can identified the transaction (child) tables.

In the above scenario, we can identified 4 different main objects whose information should be contained in the master tables. They are given below:-

1) Employee

In this master table, we store the information about the employee, like name, date of joining etc.

CREATE TABLE [dbo].[tbl_employees](
    [employeeid] [int] IDENTITY(1,1) NOT NULL,
    [firstname] [nvarchar](100) NULL,
    [lastname] [nvarchar](100) NULL,
    [regdatum] [datetime] NULL,
     [nvarchar](100) NULL,
 CONSTRAINT [PK_tbl_employees] PRIMARY KEY CLUSTERED
(
    [employeeid] ASC
) )ON [PRIMARY]

Suppose we have to store the employee data as shown in the above picture then the insert command for the table tbl_employee will be as follows:

Insert into tbl_employees(firstname,lastname,regdatum,email)
select 'Vivek','Johari','02/01/2007','vivek@xyz.com'
union all
select 'Mohit','Rathi','03/01/2013','mohit@xyz.com'
union all
select 'Aviral','Lamba','05/01/2007','aviral@xyz.com'
union all
select 'Chandra','Singh','10/01/2010','chandra@xyz.com'
union all
select 'Abhinav','Golwalkar','01/01/2007','abhinav@xyz.com'
union all
select 'Gaurav','Negi','05/01/2013','gaurav@xyz.com'

2) Projects

In this master table,we store the information about the projects like their name, start date, end date

CREATE TABLE [dbo].[tbl_project](
    [projectid] [int] IDENTITY(1,1) NOT NULL,
    [projectname] [nvarchar](200) NULL,
    [startdate] [datetime] NULL,
    [enddate] [datetime] NULL,
 CONSTRAINT [PK_tbl_project] PRIMARY KEY CLUSTERED
(
    [projectid] ASC
)
) ON [PRIMARY] 

 Similarly, suppose we have to store the above projects information as shown in the above picture then the insert command for the table tbl_project will be given below:


 Insert into tbl_project(projectname,startdate, enddate)
select 'Learninglms','02/01/2007','10/01/2010'
union all
select 'Bankpds','05/01/2013','05/01/2014'
union all
select 'ERPsystem','05/01/2012','10/01/2014'

3) Designation

In this master table, we store information about the various designations of the company which they have in their hierarchy.

CREATE TABLE [dbo].[tbl_designation](
    [designationid] [int] IDENTITY(1,1) NOT NULL,
    [designationname] [nvarchar](150) NULL,
 CONSTRAINT [PK_tbl_designation] PRIMARY KEY CLUSTERED
(
    [designationid] ASC
)
) ON [PRIMARY]

Similarly, to store all the designation of the company as shown in above figure, the insert command for the table tbl_designation is given below:

Insert into tbl_designation(designationname)
select 'Trainee'
union all
select 'Software Engineer'
union all
Select 'Tech Laed'
union all
select 'Senior Tech lead'
union all
select 'Project manager'

4) Technology

In this master table, we store the information about various technologies like name of the technology

CREATE TABLE [dbo].[tbl_technology](
    [technologyid] [int] IDENTITY(1,1) NOT NULL,
    [technologyname] [nvarchar](150) NULL,
 CONSTRAINT [PK_tbl_technology] PRIMARY KEY CLUSTERED
(
    [technologyid] ASC
)
) ON [PRIMARY]

Similarly, to store all the various technologies used in the company (shown in the above figure), the insert command for the table tbl_technology is given below:

Insert into tbl_technology(technologyname)
select 'JAVA'
union all
select 'C#'
union all
select 'CSS'
union all
select 'JQUERY'
union all
select 'SQL Server'
union all
select 'ORACLE'
union all
select 'PHP'
union all
select 'HTML5'

Employee-Project relationship

In any company, an employee can work in multiple project and in one project multiple employees can work. So there is many to many relationship between employees and projects. In these case we need to create a transaction(child or bridge) table to store these relationship whose structure is given below:-

CREATE TABLE [dbo].[tbl_employee_project](
    [empprojectid] [int] IDENTITY(1,1) NOT NULL,
    [employeeid] [int] NULL,
    [projectid] [int] NULL,
    [allocationstartdate] [datetime] NULL,
    [allocationenddate] [datetime] NULL
) ON [PRIMARY]

GO

Foreign Key relationship with their master table is given below:-

Here, employeeid of the table tbl_employee_project refers(foreign key) to employeeid of the master table tbl_employees.

ALTER TABLE [dbo].[tbl_employee_project]  WITH CHECK ADD  CONSTRAINT [FK_tbl_employee_project_tbl_employees] FOREIGN KEY([employeeid])
REFERENCES [dbo].[tbl_employees] ([employeeid])
GO

ALTER TABLE [dbo].[tbl_employee_project] CHECK CONSTRAINT [FK_tbl_employee_project_tbl_employees]
GO

Here, projectid of the table tbl_employee_project refers(foreign key) to projectid of the master table tbl_project.

ALTER TABLE [dbo].[tbl_employee_project]  WITH CHECK ADD  CONSTRAINT [FK_tbl_employee_project_tbl_project] FOREIGN KEY([projectid])
REFERENCES [dbo].[tbl_project] ([projectid])
GO

ALTER TABLE [dbo].[tbl_employee_project] CHECK CONSTRAINT [FK_tbl_employee_project_tbl_project]
GO

Now suppose we want to allocate the employees to the various projects as shown in the above figure then the insert command for the table  tbl_employee_project is given below:

Insert into tbl_employee_project (employeeid,projectid, allocationstartdate, allocationenddate)
select 1,2,'05/01/2013',null
union all
select 1,3,'05/01/2012','05/20/2013'
union all
select 2,1,'02/01/2007','10/01/2010'
union all
select 3,1,'02/01/2007','10/01/2010'
union all
select 4,3,'05/01/2012',null
union all
select 5,2,'05/01/2013',null
union all
select 5,3,'05/01/2012',null
union all
select 6,3,'05/01/2012',null

Here in these insert command, we use the employeeid of the employees taken from table tbl_employees and projectid of the projects taken from the table tbl_project.

Employee designation relationship

In a company, an employee can have one designation at a time . For example, an employee can't be software engineer or tech lead at a same time in the company. He is either software engineer or tech lead. But we also need to store the history of the  employee previous designation so that we can maintain the record that 6 months back, at which designation, a particular employee is work in project. For example , suppose 6 month back employee, "Vivek" work as software engineer to an project "ABC LMS" and now he become tech lead and working in another project "XYZ LMS" as tech lead. So to maintain that history, we have to store the employee designation relationship in a separate transaction table. Now a employee designation within a longer duration of time can change from one position to another position and on same designation, multiple employee can work, there is many to many relationship.

The structure to store this relationship is given below:-

CREATE TABLE [dbo].[tbl_emp_designation](
    [empdesignationid] [int] IDENTITY(1,1) NOT NULL,
    [employeeid] [int] NULL,
    [designationid] [int] NULL,
    [allocationdate] [datetime] NULL
) ON [PRIMARY]

GO

Foreign Key relationship with their master table is given below:-

ALTER TABLE [dbo].[tbl_emp_designation]  WITH CHECK ADD  CONSTRAINT [FK_tbl_emp_designation_tbl_designation] FOREIGN KEY([designationid])
REFERENCES [dbo].[tbl_designation] ([designationid])
GO

ALTER TABLE [dbo].[tbl_emp_designation] CHECK CONSTRAINT [FK_tbl_emp_designation_tbl_designation]
GO

Here, employeeid of the table tbl_emp_designation refers(foreign key) to employeeid of the master table tbl_employees.


ALTER TABLE [dbo].[tbl_emp_designation]  WITH CHECK ADD  CONSTRAINT [FK_tbl_emp_designation_tbl_employees] FOREIGN KEY([employeeid])
REFERENCES [dbo].[tbl_employees] ([employeeid])
GO

ALTER TABLE [dbo].[tbl_emp_designation] CHECK CONSTRAINT [FK_tbl_emp_designation_tbl_employees]

Now suppose we want to allocate designation to all the employees as shown in the above figure then the insert command for the table  tbl_emp_designationis given below:

Insert into tbl_emp_designation(employeeid,designationid,allocationdate)
select 1,3,'01/06/2012'
union all
select 1,4,getdate()
union all
select 2, 2, '01/06/2012'
union all
select 3,1,'01/06/2013'
union all
select 4,2,'10/01/2010'
union all
select 4,3,getdate()
union all
select 5,3 ,'01/01/2007'
union all
select 5,4,'01/06/2012'
union all
select 5,5,getdate()
union all
select 6,2,'05/01/2013'

Here also we use the employeeid of each employee from the table tbl_employees and designationid from the table tbl_designation.

Employee technology relationship

An employee can have the knowledge of more than one technology. For example , an employee can know, SQL server, Asp.Net, CSS, JQuery, JAVA Script, C# and on a particular technology can be known by multiple employees, so there is again many to many relationship exists between employees and technologies. Therefore we need a transaction table to store the relationship between the employee and technologies.

CREATE TABLE [dbo].[tbl_employee_technology](
    [emptechnologyid] [int] IDENTITY(1,1) NOT NULL,
    [employeeid] [int] NULL,
    [technologyid] [int] NULL
) ON [PRIMARY]

GO

Foreign Key relationship with their master table is given below:-

ALTER TABLE [dbo].[tbl_employee_technology]  WITH CHECK ADD  CONSTRAINT [FK_tbl_employee_technology_tbl_employees] FOREIGN KEY([employeeid])
REFERENCES [dbo].[tbl_employees] ([employeeid])
GO

ALTER TABLE [dbo].[tbl_employee_technology] CHECK CONSTRAINT [FK_tbl_employee_technology_tbl_employees]
GO

Here, technologyid of the table tbl_employee_technology refers(foreign key) to technologyid of the master table tbl_technology.

 ALTER TABLE [dbo].[tbl_employee_technology]  WITH CHECK ADD  CONSTRAINT [FK_tbl_employee_technology_tbl_technology] FOREIGN KEY([technologyid]) REFERENCES [dbo].[tbl_technology] ([technologyid])
GO
ALTER TABLE [dbo].[tbl_employee_technology] CHECK CONSTRAINT [FK_tbl_employee_technology_tbl_technology]

Again to store the various technologies known to an employee, as shown in the above figure,  we use the following insert command for the table  tbl_employee_technology.

Insert into tbl_employee_technology(employeeid,technologyid)
select 1,6
union all
select 1,5
union all
select 1,1
union all
select 1,8
union all
select 1,3
union all
select 2,3
union all
select 2,8
union all
select 2,4
union all
select 3,3
union all
select 3,8
union all
select 3,4
union all
select 4,5
union all
select 4,2
union all
select 4,4
union all
select 5,5
union all
select 5,2
union all
select 5,4
union all
select 6,1
union all
select 6,3
union all
select 6,4

Here , since one employee can have the knowledge of multiple technologies, so we insert one row for each technology known by the employee. For example, since the employee "Vivek Johari" knows 5 technologies, so insert 5 rows for this employee with one row per technology. 

Project Technology relationship

An Enterprise application , works on multiple technologies like they use asp.net with c#/vb.net for server side front end coding, java script, css, jquery for client side coding and SQL server/ Oracle/ My SQL for storing the database. So in this way, a project can include multiple technologies. Similarly, multiple projects can work on asp.net or java script or css. So there is many to many relationship between projects and technologies. So we need a transaction table to store that relationship.

CREATE TABLE [dbo].[tbl_project_technology](
    [projecttechnologyid] [int] IDENTITY(1,1) NOT NULL,
    [projectid] [int] NULL,
    [technologyid] [int] NULL
) ON [PRIMARY]

GO

Foreign Key relationship with their master table is given below:-

ALTER TABLE [dbo].[tbl_project_technology]  WITH CHECK ADD  CONSTRAINT [FK_tbl_project_technology_tbl_project] FOREIGN KEY([projectid])
REFERENCES [dbo].[tbl_project] ([projectid])
GO

ALTER TABLE [dbo].[tbl_project_technology] CHECK CONSTRAINT [FK_tbl_project_technology_tbl_project]
GO
Here, technologyid of the table tbl_project_technology refers(foreign key) to technologyid of the master table tbl_technology.

ALTER TABLE [dbo].[tbl_project_technology]  WITH CHECK ADD  CONSTRAINT [FK_tbl_project_technology_tbl_technology] FOREIGN KEY([technologyid])
REFERENCES [dbo].[tbl_technology] ([technologyid])
GO

ALTER TABLE [dbo].[tbl_project_technology] CHECK CONSTRAINT [FK_tbl_project_technology_tbl_technology]
GO

Again to store the various technologies used to a project, as shown in the above figure, we use the following insert command for the table tbl_project_technology. Here instead of storing the project name and technology name , we use their id taken from there respective master table.

Insert into tbl_project_technology(projectid,technologyid)
Select 1, 8
union all
select 1, 4
union all 
select 1, 3
union all
select 2, 2
union all
select 2,5
union all
select 2,4
union all
select 2,3
union all
select 3,1
union all
select 3,6
union all
select 3,4
union all
select 3,8

Here also, we used technologyid from the table tbl_technology and projectid from the table tbl_project. In this table too, if a project involves multiple technologies, we store the information about each technology for a project in a separate row.

With the help of this question, I tried to explain how to design a database tables and their relationship for a given situation. If anyone has any query regarding this, you can mail me at askvivekjohari@gmail.com. 
Waiting for your valuable feedback 🙂