LATEST 🚀 Welcome to Upgradix by Shrash Studio – Your destination for free technology learning and career growth.
🕘 Updated:
MS SQL Server

SQL Server Database Design – Build an Employee Management Database

Database design = planning how information is stored and connected. We will build an Employee Management Database using SQL Server. We will separate departments and employees into related tables. Primary Keys and Foreign Keys will maintain their relationships.

SQL Server Database Design – Build an Employee Management Database

Quick Answer: Database design = planning how information is stored and connected. We will build an Employee Management Database using SQL Server. We will separate departments and employees into related tables. Primary Keys and Foreign Keys will maintain their relationships.

1. Why Do We Need Proper Database Design?

Imagine Priya manages HR operations for a company with 1,000 employees. She needs employee names, salaries, departments and joining dates.

Every employee belongs to a department. Many employees can belong to the same department.

How would Priya store this information without repeating department details?

The problem without proper database design

We could store everything inside one Employees table. Each employee record would repeat the department information.

Example 1: Store employee and department details together

This SQL example works, but it repeats department information.

CREATE TABLE EmployeeRecords
(
    EmployeeId INT,
    EmployeeName VARCHAR(50),
    DepartmentName VARCHAR(50),
    DepartmentPhone VARCHAR(15)
);

INSERT INTO EmployeeRecords VALUES
(101, 'Rahul', 'IT', '08011110000'),
(102, 'Priya', 'IT', '08011110000'),
(103, 'Arjun', 'HR', '08022220000');

SELECT * FROM EmployeeRecords;

Expected output

EmployeeId  EmployeeName  DepartmentName  DepartmentPhone
101         Rahul         IT              08011110000
102         Priya         IT              08011110000
103         Arjun         HR              08022220000

This example stores the records correctly. However, the design has problems.

  1. IT department details are repeated for every employee.
  2. Changing a department phone requires multiple updates.
  3. Deleting the last employee could remove department information.
  4. Invalid department names can enter employee records.

Now imagine the same design with 1,000 employees and many departments.

We need separate tables for departments and employees. We also need a reliable relationship between them.

The solution: Relational Database Design

Database design = planning tables, columns and relationships before storing data.

Primary Key = a column that uniquely identifies each record.

Foreign Key = a column that references a key in another table.

Relationship = a connection between records in different tables.

We will create two tables. Departments stores department details, while Employees stores employee details.

Animation 1: How employee database tables connect

Follow Priya's HR workflow and see how SQL Server connects employees with departments.

Priya HR manager · manages employees
enters employee details
Employee Records Rahul · Priya · Arjun
organizes information
Departments PK · DepartmentId DepartmentName DepartmentPhone
Employees PK · EmployeeId EmployeeName FK · DepartmentId Salary
connects using DepartmentId
Foreign Key Employees.DepartmentId references Departments.DepartmentId
retrieves related information
Employee Report Rahul · IT · Priya · IT
Employee input Database tables Related result Current animation stage

Priya stores department details once. Employee records reference departments through DepartmentId.

2. Implement Employee Management Database in SQL Server

Let's create the database using SQL Server and T-SQL.

One-to-many relationship = one department can have many employees.

We will use DepartmentId to connect both tables.

Create the database and tables

Example 2: Create related employee and department tables

Run this complete script in SQL Server Management Studio.

CREATE DATABASE EmployeeDB;
GO
USE EmployeeDB;
GO
CREATE TABLE Departments
(
    DepartmentId INT PRIMARY KEY,
    DepartmentName VARCHAR(50) NOT NULL UNIQUE,
    DepartmentPhone VARCHAR(15)
);
CREATE TABLE Employees
(
    EmployeeId INT PRIMARY KEY,
    EmployeeName VARCHAR(50) NOT NULL,
    DepartmentId INT NOT NULL,
    Salary DECIMAL(10,2),
    FOREIGN KEY (DepartmentId)
        REFERENCES Departments(DepartmentId)
);
INSERT INTO Departments VALUES
(1, 'IT', '08011110000'),
(2, 'HR', '08022220000');
INSERT INTO Employees VALUES
(101, 'Rahul', 1, 65000),
(102, 'Priya', 1, 60000),
(103, 'Arjun', 2, 55000);
SELECT E.EmployeeName, D.DepartmentName, E.Salary
FROM Employees E
INNER JOIN Departments D
    ON E.DepartmentId = D.DepartmentId;

Notice that DepartmentId identifies each department. The same value connects employees to their department.

The UNIQUE constraint prevents duplicate department names. The Foreign Key prevents invalid department references.

The final query uses INNER JOIN to retrieve employee and department information together.

Expected output

EmployeeName  DepartmentName  Salary
Rahul         IT              65000.00
Priya         IT              60000.00
Arjun         HR              55000.00

Design tip: Create the parent table before the child table. Insert department records before employee records. This ensures that Foreign Key references are valid.

3. Quick Summary

Without proper design vs with proper design

Comparing a single employee table with a relational database design
Without proper design With proper design
Department details repeat. Department details are stored once.
Updates need multiple changes. Update department details in one place.
Invalid department values may enter. Foreign Keys enforce valid references.
Relationships are difficult to manage. Related records are retrieved using JOIN.

The important terms to remember

  • Primary Key: Uniquely identifies a table record.
  • Foreign Key: References a key in another table.
  • One-to-many: One department can have many employees.
  • Normalization: Organizing data to reduce duplication and improve consistency.
  • JOIN: Retrieves related information from multiple tables.

4. Common Mistakes and Best Practices

1. Repeating department information

Repeating department names and phone numbers creates unnecessary updates.

Better approach: Store department information in a separate table.

2. Ignoring Primary and Foreign Keys

Without keys, duplicate records and invalid relationships become harder to control.

Better approach: Define suitable keys when creating database tables.

3. Choosing incorrect data types

Storing salary as VARCHAR makes numerical calculations difficult.

Better approach: Use DECIMAL for salary and appropriate types for other columns.

5. How Employee Database Design Comes Up in Interviews

Question 1: How would you design an Employee Management Database?

What the interviewer expects: Explain tables, keys and relationships.

Strong answer: "I would create separate Employees and Departments tables. Each table would have a Primary Key. Employees would reference Departments using DepartmentId as a Foreign Key."

Question 2: Why should we avoid duplicate department information?

What the interviewer expects: Explain data duplication and consistency.

Strong answer: "Repeating department details creates update and deletion problems. I would store department information separately and connect it using a Foreign Key."

Question 3: What is a one-to-many relationship?

What the interviewer expects: Explain the relationship with an example.

Strong answer: "One department can have many employees. Each employee references a department through DepartmentId."

What interviewers expect at different levels

  • Junior developer: Create tables and define Primary and Foreign Keys.
  • Senior developer: Explain normalization, constraints and indexing choices.
  • Technical architect: Discuss scalability, data ownership, relationships and database changes.

6. Hands-On Practice

Beginner: School Management Database

Create Students and Classes tables. Connect each student to a class using ClassId.

Expected result: Retrieve student names with their class names.

Intermediate: Hospital Management Database

Create Doctors, Patients and Appointments tables. Connect appointments with doctors and patients.

Hint: Use two Foreign Keys inside the Appointments table.

Advanced: Extend Employee Management Database

Extend Priya's database with additional employee information.

  • Add a Designation column to Employees.
  • Create a separate Locations table.
  • Connect employees with their assigned locations.

Challenge: Design the relationships and retrieve complete employee details without repeating department information.

7. Next Steps and Community

Previous article: SQL Server Locking, Blocking, Deadlocks and Concurrency

Next article: Real-World SQL Queries – Solve Business Reporting Problems

For more details, read the official Microsoft documentation:

Try extending Priya's Employee Management Database. Add more departments, employees and relationships. Share your database design and discuss your implementation with other learners.

Comments & Ratings (0)

Log in or create a student account to comment and rate this article.

Be the first to comment on this article.