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.
- IT department details are repeated for every employee.
- Changing a department phone requires multiple updates.
- Deleting the last employee could remove department information.
- 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 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
| 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.