How to design the table structure of the warehouse management system in MySQL to manage warehouse employee information?

How to design the table structure of the warehouse management system in MySQL to manage warehouse employee information?

In the warehouse management system, employee information is an important part, and its table structure design should be able to store basic employee information and related warehouse management data. When designing the table structure of the warehouse management system in MySQL, it can be divided into multiple tables according to the attributes of employee information, and primary keys and foreign keys can be used to establish relationships between tables.

The following is an example of table structure design for warehouse employee information:

1. Employee table (Employees)

   • Employee number (emp_id) - Primary key, self-increasing
   • Name(name)
   • Gender(gender)
   • Birthdate(birth_date)
   • Mobile phone number(phone_number)
   • Email (email)

CREATE TABLE Employees (

emp_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
gender ENUM('男', '女'),
birth_date DATE,
phone_number VARCHAR(11),
email VARCHAR(50)

);

2. Department table ( Departments)

   • Department number (dept_id) - primary key, auto-increment
   • Department name (dept_name)

CREATE TABLE Departments (

dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(50)

);

3. Employee-Department relationship table (Employee_Department)

   • Relationship number (rel_id) - primary key, auto-increment
   • Employee number (emp_id) - foreign key, related to the employee table
   • Department number (dept_id) - foreign key, related to the department table

CREATE TABLE Employee_Department (

rel_id INT PRIMARY KEY AUTO_INCREMENT,
emp_id INT,
dept_id INT,
FOREIGN KEY (emp_id) REFERENCES Employees(emp_id),
FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)

);

4. Work record table (Work_Record)

   • Record number (record_id) - primary key, Self-increasing
   • Employee number (emp_id) - foreign key, related to the employee table
   • Join date (join_date)
   • Leave date (leave_date)
   • Position (position)

CREATE TABLE Work_Record (

record_id INT PRIMARY KEY AUTO_INCREMENT,
emp_id INT,
join_date DATE,
leave_date DATE,
position VARCHAR(50),
FOREIGN KEY (emp_id) REFERENCES Employees(emp_id)

);

5. ## Salary (Salary)
   Salary number (salary_id) - primary key, self-increasing
   • Employee number (emp_id) - foreign key, associated to the employee table
   • month(month)
   • Salary(salary)

CREATE TABLE Salary (

salary_id INT PRIMARY KEY AUTO_INCREMENT,
emp_id INT,
month DATE,
salary DECIMAL(10, 2),
FOREIGN KEY (emp_id) REFERENCES Employees(emp_id)

);

The above is a simple warehouse employee information table structure Design examples that can be adapted and expanded based on actual needs. Through the design of these tables, the management and recording of warehouse employee information can be achieved, and subsequent inquiries and statistics can be facilitated. In actual applications, other tables and fields can be added as needed, such as permission tables, attendance tables, etc., to meet more complex system requirements.

The above is the detailed content of How to design the table structure of the warehouse management system in MySQL to manage warehouse employee information?. For more information, please follow other related articles on the PHP Chinese website!