oracle 员工表（主表和明细表）

Employee table (Master and detail table)

提问by user1263981

I am wondering if it is okay to have master and detail table for employees?

我想知道是否可以为员工提供主表和明细表？

As per requirments, data can filtered by department by country and by employee code on report level.

根据要求，数据可以按国家/地区的部门和报表级别的员工代码进行过滤。

If employee's department or country code is changed then the changes will go in detail table and old record will be set to IS_ACTIVE = 'T'.

如果员工的部门或国家/地区代码发生​​更改，则更改将进入详细表，旧记录将设置为 IS_ACTIVE = 'T'。

---------------------Master Table--------------------------------------

---------------------主表------------------------ -----------

**EMPLOYEE_CODE**      VARCHAR2(20 BYTE)  NOT NULL, 
EMAIL                  VARCHAR2(100 BYTE)
FIRST_NAME             VARCHAR2(50 BYTE)
LAST_NAME              VARCHAR2(50 BYTE)
WORKING_HOURS          NUMBER

---------------------Detail Table--------------------------------------

---------------------详细表--------------------------- -----------

**PK_USER_DETAIL_ID**     NUMBER,
FK_EMPLOYEE_CODE          VARCHAR2(20 BYTE),
FK_GROUP                  NUMBER,
FK_DEPARTMENT_CODE        NUMBER,
FK_EMPLOYER_COUNTRY_CODE  VARCHAR2(5 BYTE),
FK_MANAGER_ID             VARCHAR2(20 BYTE),
FK_ROLE_CODE              VARCHAR2(6 BYTE),
START_DATE                DATE,
END_DATE                  DATE,
IS_ACTIVE                 VARCHAR2(1 BYTE),
INACTIVE_DATE             DATE

---

---

Employee table will be linked with Timesheet table and for timesheet reports data can be filtered by department, country and by employee code.

员工表将与时间表表相关联，时间表报告的数据可以按部门、国家和员工代码过滤。

OPTION : I

选项：我

• Have one employee table with one Primary Key and create a new entry whenever department or role is updated for an employee.
• Add country and department code in the timesheet table.

• 有一个带有一个主键的员工表，并在为员工更新部门或角色时创建一个新条目。
• 在时间表表中添加国家和部门代码。

--> This way i don't need to search employee table.

--> 这样我就不需要搜索员工表了。

OPTION : II

选项：二

• Have master and detail table.
• Add country and department code in timesheet table.

• 有主表和明细表。
• 在时间表表中添加国家和部门代码。

--> This way i don't need to search employee table plus i will have master detail table

--> 这样我就不需要搜索员工表，而且我会有主从表

OPTION : NEW

选项：新

• Have master and detail table.
• Timesheet table will have EmpCode.
• If user move to new locationor change departmentthen Insert a new row in the detail table with the new dept Code and same Emp No.
• Update an old row and set the End Date field so if he changes his location or department then the End Date field needs to be updated.

• 有主表和明细表。
• 时间表表将有 EmpCode。
• 如果用户移动到新位置或更改部门，则使用新的部门代码和相同的员工编号在明细表中插入一个新行。
• 更新旧行并设置结束日期字段，因此如果他更改位置或部门，则需要更新结束日期字段。

Which one is a best option and is there any other better option available?

哪一个是最好的选择，还有其他更好的选择吗？

回答by APC

This is one way of implementing this requirement, and it's an approach many people take. However, it has on emajor drawback: every time you query the currentemployee status you need to filter the details on start and end date. This may seem like a trivial thing, but you wouldn't believe how much confusion it can cause, and it has performance implications too.

这是实现这一要求的一种方式，也是许多人采用的一种方法。但是，它有一个主要缺点：每次查询当前员工状态时，都需要过滤开始和结束日期的详细信息。这似乎是一件微不足道的事情，但您不会相信它会引起多少混乱，而且它也对性能有影响。

These things matter, because most of the time you will want only the current details, with queries on history being a relatively rare occurence. Consequently you are hampering the implementation of your most common use case to make it easier to implement a less-used one. (Of course I am making assumptions about your business requirements, and perhaps yours is not a run-of-the-mill employee application...)

这些事情很重要，因为大多数时候您只需要当前的详细信息，而历史查询相对较少发生。因此，您阻碍了最常见用例的实现，以便更轻松地实现较少使用的用例。（当然，我是在对您的业务需求做出假设，也许您的需求不是普通的员工应用程序......）

The better solution would be to have two tables, an EMPLOYEES table with all the detail columns too and an EMPLOYEES_HISTORY table with the same columns plus the start and end date. When you change an employee's record insert a copy of the old record in the History table, probably by a trigger. Your standard processes have just the one table to query, and your history needs are met fully.

更好的解决方案是拥有两个表，一个包含所有详细信息列的 EMPLOYEES 表和一个包含相同列以及开始和结束日期的 EMPLOYEES_HISTORY 表。当您更改员工的记录时，可能通过触发器在 History 表中插入旧记录的副本。您的标准流程只有一张表可供查询，并且完全满足您的历史需求。

---

---

By the way, your proposed data model is wrong. Working_hours, email_address and last_name are definitely things which can change and perhaps even first name (e.g. through changes in personal circumstances such as getting married). So all those columns should be held in your details name

顺便说一句，您提出的数据模型是错误的。Working_hours、email_address 和 last_name 绝对是可以改变的东西，甚至可能改变名字（例如，通过结婚等个人情况的变化）。因此，所有这些列都应保存在您的详细信息名称中

回答by Pankaj

Option 3 - Please note that this option is useful only for the reports Point of View.

选项 3 - 请注意，此选项仅对报告观点有用。

1. Whenever you insert the data, create a De-Normalizedentry in a new table.
2. Whenever an entry will be updated, the De-Normalizedentry will be updated in the new table.
3. The New Table will have all De-Normalizedcolumns of Employee.
4. So while Performing the search, this will benefit you as the results will be calculated without using Joins. Thus, the access time will be reduced.
5. Records in the new table will be Created/Updated in The Insert/Update Trigger.

1. 每当您插入数据时，请De-Normalized在新表中创建一个条目。
2. 每当更新条目时，该De-Normalized条目将在新表中更新。
3. 新表将包含De-Normalized员工的所有列。
4. 因此，在执行搜索时，这将使您受益，因为计算结果时不使用Joins. 因此，访问时间将减少。
5. 新表中的记录将在插入/更新触发器中创建/更新。

Improvements in Option - 2 and Option 1

选项 - 2 和选项 1 的改进

Don't create redundancy by adding duplicate columns.

不要通过添加重复的列来创建冗余。