Employee and Salary Table Structure in SQL

Q: Can one employee have multiple salary records?

Yes. You can allow multiple rows per id to track salary revisions over time, or add a PRIMARY KEY on (id, effective_date).

Q: What is the primary key of the salary table?

In this basic design, id is only a foreign key. For production, consider adding a surrogate primary key or a composite key.

Q: What SQL operations can I perform on these tables?

Common operations include SELECT with JOIN to combine employee and salary data, aggregate functions like SUM and AVG for salary reporting, and self-joins to display manager-subordinate relationships.

Designing an Employee and Salary table structure is one of the most common tasks in SQL interviews and real-world HR/payroll systems. In this tutorial, you'll learn the schema, relationships, and CREATE TABLE scripts needed to build a clean employee–salary database in Oracle SQL, and then practice with interview queries built on these same tables.

1. Schema Overview

We use two related tables: employee stores identity and reporting information, while salary stores compensation details. Both tables are linked via the shared id column.

employee Table

id — NUMBER, primary key, unique identifier for each employee.
empname — VARCHAR2(20), employee name (NOT NULL).
birth_date — DATE, employee's birth date (NOT NULL).
mgr_id — NUMBER, self-referencing foreign key to employee(id) for manager relationships.

salary Table

id — NUMBER, foreign key to employee(id).
designation — VARCHAR2(25), job title (NOT NULL).
salary — NUMBER, monthly/base salary amount.

Relationship Summary

The employee table has a self-referential foreign key (mgr_id) to represent manager–subordinate chains.
The salary table has a foreign key id pointing to employee, linking compensation to each employee.

2. CREATE TABLE Scripts

Employee Table

CREATE TABLE employee (
    id          NUMBER PRIMARY KEY,
    empname     VARCHAR2(20) NOT NULL,
    birth_date  DATE NOT NULL,
    mgr_id      NUMBER REFERENCES employee(id)
);

The employee table stores core identity information. The mgr_id column creates a self-referencing link so each employee can point to their manager within the same table.

Salary Table

CREATE TABLE salary (
    id           NUMBER REFERENCES employee(id),
    designation  VARCHAR2(25) NOT NULL,
    salary       NUMBER
);

The salary table stores designation and salary details. Its id column is a foreign key to employee(id), ensuring every salary record maps to a valid employee.

Employee and Salary table structure diagram in SQL

3. Sample Data

Employee Table (sample rows)

id	empname	birth_date	mgr_id
1	Amit Sharma	1985-04-25	NULL
2	John Smith	1990-02-15	1
3	Priya Desai	1980-08-30	1
4	Robert Johnson	1995-11-05	2
5	Suresh Kumar	1992-06-18	3

Salary Table (sample rows)

id	designation	salary
1	Senior Developer	120000
2	Software Engineer	80000
3	Project Manager	150000
4	Junior Developer	60000
5	Team Lead	110000

With these two tables in place, you can start writing real queries. See Top Queries on Employee Salary Management for the full set.

4. Frequently Asked Questions

Why is `mgr_id` a self-referencing foreign key?

It allows an employee row to reference another row in the same table, modelling the manager–subordinate hierarchy without a separate table.

Can one employee have multiple salary records?

Yes — you can allow multiple rows per id to track salary revisions over time, or add a PRIMARY KEY on (id, effective_date).

What is the primary key of the salary table?

In this basic design, id is only a foreign key. For production, consider adding a surrogate primary key or a composite key.

What SQL operations can I perform on these tables?

Common operations include: SELECT with JOIN to combine employee and salary data, aggregate functions like SUM and AVG for salary reporting, and self-joins to display manager–subordinate relationships.