我应该标准化时态表中的额外字段吗？
我正在设计一个排班应用程序，用于解决排班人员在应用程序的生命周期内可能被多次部署的情况。
我当前的模型在每个部署记录中存储附加数据字段。
在 90% 的情况下，这些数据在部署期间不会发生变化，但在某些情况下会发生变化。目前，我只是结束当前的部署并创建一个新的部署并更新相关数据。
Staff Table
-----------------------------
| ID | First Name | Surname |
-----------------------------
| 1 | Bob | Brown |
-----------------------------
Deployments Table
------------------------------------------------------------
| Sta | End | Staff_ID | Reg Pay | Temp Pay | Other Fields |
------------------------------------------------------------
| Jan | Mar | 1 | 3 | 3 | Other data |
| Jul | Sep | 1 | 3 | 5 | Other data |
| Sep | Dec | 1 | 5 | 5 | Other data |
------------------------------------------------------------
示例：Bob 的薪资级别为 3。他于 2011 年 1 月首次按正常薪资级别部署。他的部署于 2011 年 3 月结束（因此从该日起不再出现在名单上）。
七月，他被重新调动，但这一次的薪酬水平高于他的正常水平。
9 月，他的常规职位晋升为与他的部署薪资水平一致（即，当他的部署结束后，他将继续保持 5 级）。
7 月的部署于 12 月完成，但实际上包含两条记录。
我提出这个模型是为了尝试减少我必须做的日期周期连接处理的数量，并且在我需要添加新的特定于部署的字段时可以轻松更新界面。
部署表增长超过一千条记录的可能性极小，因为应用程序作为一个整体是针对其本质上持续时间有限的情况而设计的。
我的问题：我是搬起石头砸自己的脚吗？
这是一个绝对糟糕的数据结构吗？我应该硬着头皮将这些数据提取到单独的表中吗？有没有什么方法可以做到这一点，而不增加我每次添加字段时都必须处理的开始/结束日期字段的数量？
I am designing a rostering application for a situation where the staff being rostered may be deployed multiple times over the lifetime of the application.
My current model stores additional data fields in each deployment record.
In 90% of cases this data will not change during a deployment, but there will be some cases where it will. At the moment, I simply end the current deployment and create a new one with the relevant data updated.
Staff Table
-----------------------------
| ID | First Name | Surname |
-----------------------------
| 1 | Bob | Brown |
-----------------------------
Deployments Table
------------------------------------------------------------
| Sta | End | Staff_ID | Reg Pay | Temp Pay | Other Fields |
------------------------------------------------------------
| Jan | Mar | 1 | 3 | 3 | Other data |
| Jul | Sep | 1 | 3 | 5 | Other data |
| Sep | Dec | 1 | 5 | 5 | Other data |
------------------------------------------------------------
Example: Bob's pay level is 3. He is first deployed in January 2011 at his regular pay level. His deployment ends in March 2011 (and therefore ceases to show on the roster from that date).
In July he is re-deployed, but this time at a higher pay level than his regular level.
In September his regular position is promoted to be in line with his deployment pay level (i.e. when his deployment finishes he will continue at level 5).
The July deployment finishes in December, but actually consists of two records.
I came up with this model to try and reduce the amount of date-period-join-grappling that I had to do, as well as to make it easy to update the interface if I need to add new deployment-specific fields.
It is extremely unlikely that the deployment table will grow much over a thousand records, as the application as a whole is designed for situations that are by their nature limited in duration.
My question: Am I shooting myself in the foot?
Is this an absolutely awful data structure? Should I bite the bullet and extract this data out into separate tables? Is there any way of doing this without multiplying the number of start/end date fields that I have to deal with any time I add a field?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我发现了一个可以在时态表上使用的好技巧：添加一个特殊的列，我将其称为连续性 ID，因为没有更好的术语。此列等于记录的主键，该记录打开一组代表您正在建模的相同项目或操作（在您的情况下为部署）的相关记录。假设示例 1、2 和 3 中三行的主键。那么第一条记录的连续性 ID 将为 1；其他两条记录的连续性 ID 将为 2，因为它们代表相同的部署，并且属于它们代表的部署的第一条记录的主键为 2。
使用连续性 ID 列后，您可以添加其他字段进入您的时态表，并能够相对轻松地回答有关部署发生情况的问题：给定组中一条记录的 ID（例如，最新的），您可以轻松查询开始日期和结束日期，例如项目随时间的历史记录，针对项目的状态例如特定时间等等。
I discovered a nice trick that you can play on your temporal tables: add a special column that I call continuity ID for lack of a better term. This column is equal to the primary key of the record that opens a group of related records representing the same item or action that you are modeling - in your case, a deployment. Let's say the primary keys of the three rows in your example 1, 2, and 3. Then the continuity ID of the first record would be 1; the continuity IDs of the other two records would be 2, because they represent the same deployment, and the first record that belongs to the deployment they represent has the primary key of 2.
With the continuity ID column in place, you can put additional fields into your temporal table, and be able to answer questions about what happened to your deployment relatively easily: given an ID of one of the records in the group (say, the most recent) you can easily query for the beginning and end dates, for the history of the item over time, for a state of the item as of a particular time, et cetera.