【数据分析面试】54.员工信息(HR)数据库搭建

题目

由于发展需求，进一步提高公司人员统筹管理的能力，公司决定要重新升级人力数据管理系统。

现在，你的任务是为公司重新设计和搭建一个员工信息数据库。

提示：考虑HR管理系统的功能，比如人员信息、入职时间、离职时间、薪资数据等等。

数据库表设计

1. Employees（员工表）

存储员工的基本信息。

• EmployeeID: INT, 主键
• FirstName: VARCHAR(50)
• LastName: VARCHAR(50)
• Gender: CHAR(1) (M/F)
• BirthDate: DATE
• HireDate: DATE
• PositionID: INT, 外键（引用Positions表）
• DepartmentID: INT, 外键（引用Departments表）
• ManagerID: INT, 外键（引用本表）
• Email: VARCHAR(100)
• Phone: VARCHAR(15)
• Address: VARCHAR(200)
• City: VARCHAR(50)
• State: VARCHAR(50)
• ZipCode: VARCHAR(10)
• EmploymentStatus: VARCHAR(20)
• EmergencyContactName: VARCHAR(100)
• EmergencyContactPhone: VARCHAR(15)
• EmergencyContactRelation: VARCHAR(50)
• TerminationDate: DATE
• TerminationReason: VARCHAR(255)

2. Departments（部门表）

存储公司的部门信息。

• DepartmentID: INT, 主键
• DepartmentName: VARCHAR(100)
• Location: VARCHAR(100)
• ManagerID: INT, 外键（引用Employees表）
• CreatedDate: DATE
• UpdatedDate: DATE

3. Positions（职位表）

存储职位信息。

• PositionID: INT, 主键
• PositionTitle: VARCHAR(100)
• SalaryGrade: INT
• DepartmentID: INT, 外键（引用Departments表）
• JobDescription: TEXT
• CreatedDate: DATE
• UpdatedDate: DATE

4. Salaries（薪资表）

存储员工的薪资信息。

• SalaryID: INT, 主键
• EmployeeID: INT, 外键（引用Employees表）
• SalaryAmount: DECIMAL(10, 2)
• EffectiveDate: DATE
• EndDate: DATE

5. Attendances（考勤表）

存储员工的考勤记录。

• AttendanceID: INT, 主键
• EmployeeID: INT, 外键（引用Employees表）
• Date: DATE
• CheckInTime: TIME
• CheckOutTime: TIME

6. PerformanceReviews（绩效评估表）

存储员工的绩效评估记录。

• ReviewID: INT, 主键
• EmployeeID: INT, 外键（引用Employees表）
• ReviewDate: DATE
• ReviewerID: INT, 外键（引用Employees表）
• Rating: INT
• Comments: TEXT
• Goals: TEXT

说明

1. Employees表包含了员工的基本信息，并通过外键关联到Departments和Positions表。每个员工可能有一个上级经理，这通过ManagerID来实现自引用。
2. Departments表记录了部门信息，每个部门可以有一个经理。
3. Positions表记录了职位信息，包括薪资等级和所属部门。
4. Salaries表存储了员工的薪资记录，允许追踪薪资的变化。
5. Attendances表记录了员工每日的考勤情况。
6. PerformanceReviews表记录了员工的绩效评估信息，包括评分和评论。

这个Schema设计可以覆盖大部分HR系统的基本需求，但在实际应用中可能还需要根据具体需求进行扩展和修改。

代码示例（部分）

以下是SQL代码，可以创建相应的表并处理外键引用，以确保表能够正确创建。注意表的创建顺序以及外键约束的处理。

-- 创建 Departments 表
CREATE TABLE Departments (DepartmentID INT PRIMARY KEY,DepartmentName VARCHAR(100),Location VARCHAR(100),ManagerID INT,CreatedDate DATE,UpdatedDate DATE
);-- 创建 Positions 表
CREATE TABLE Positions (PositionID INT PRIMARY KEY,PositionTitle VARCHAR(100),SalaryGrade INT,DepartmentID INT,JobDescription TEXT,CreatedDate DATE,UpdatedDate DATE,FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);-- 创建 Employees 表
CREATE TABLE Employees (EmployeeID INT PRIMARY KEY,FirstName VARCHAR(50),LastName VARCHAR(50),Gender CHAR(1) CHECK (Gender IN ('M', 'F')),BirthDate DATE,HireDate DATE,PositionID INT,DepartmentID INT,ManagerID INT,Email VARCHAR(100),Phone VARCHAR(30),Address VARCHAR(200),City VARCHAR(50),State VARCHAR(50),ZipCode VARCHAR(10),EmploymentStatus VARCHAR(20),EmergencyContactName VARCHAR(100),EmergencyContactPhone VARCHAR(30),EmergencyContactRelation VARCHAR(50),TerminationDate DATE,TerminationReason VARCHAR(255),FOREIGN KEY (PositionID) REFERENCES Positions(PositionID),FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID),FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);-- 更新 Departments 表以增加外键约束
ALTER TABLE Departments
ADD CONSTRAINT fk_departments_manager
FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID);

ER图 （参考）

*更多详细答案可查看原文链接或关注公众号查阅。