Employee and Salary Table Structure & Guidelines

Employee and Salary Table Structure and Data Guidelines

1.employee Table: id: NUMBER ;— primary key, unique identifier of each employee.
empname: VARCHAR2(20); — name of the employee cannot be null.
birth_date: DATE ;— birthdate of the employee cannot be null.
mgr_id: NUMBER ;— foreign key referencing the id of another employee which sets up self-referential relationship of managers.

2.salary Table:
id: NUMBER; — Foreign key referencing the id from the table employee linking each record to a particular employee.
designation: VARCHAR2(25) ;— Employee designation or designation non-nullable.
salary: NUMBER ;— Salary of the employee.
Summary of Relationship
•Employee has self-referential foreign key (mgr_id) that is to be used in order to present managers.
• The table salary has a foreign key id, which points to the employee table that links the salary data with the respective employees.

Creating table
1.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)
);
Description
The employee table stores some basic information about every employee in the organization.
These columns are:
id: Unique identifier for a particular employee (primary key).
empname: The name of the employee which cannot be NULL.
birth_date: The birth date of the employee stored as a date also non-nullable.
mgr_id: Self-referencing foreign key that links an employee with his manager if any. It references id of other employee in same table in order to set a chain of managers and subordinates.

2.salary Table:

CREATE TABLE salary (
id NUMBER REFERENCES employee(id),
designation VARCHAR2(25) NOT NULL,
salary NUMBER
);
Description:
The salary table is devised in order to store relevant data of an employee's salary and their respective designation.
It has following columns:
id: which would be foreign key as referential integrity will be placed through id column of table "employee". This helps create the linkage of several related records with a employee by that particular id-column in table "employee."
designation: The job title or designation of the employee, which is to be required.
Salary: It is the amount of actual salary for an employee. This column contains numeric values, it can hold the amount of base salary or another compensation detail.
These tables are related through the id field in both employee and salary tables, reflecting the relationship between employee information and their compensation. This id in the employee table demonstrates self-referential use to illustrate employee-to-manager relationships within the same table.

Sample Data

Employee Table:


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:


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