SQL 设计 - 创建具有不同属性的同一员工的多个实例的后果
我需要为员工存储信息。每个数据库实例都是每个母公司的，下面有多个网点。在一个网点工作的一些员工也可能在其他网点工作，但是，由于每个网点在很大程度上都是自主的，每个网点都不希望其他网点看到其员工名单。
我想创建独特的员工实例，并将它们与雇用他们的网点相关联，从而在数据库中保持其详细信息的统一。然而，我的同事希望允许每个分店创建自己的员工。这种方法的结果是 John Smith 可能是 A 店的员工，B 店的 Jonathan smith 和 C 店的 J 史密斯（因为每个店几乎可以输入他们想要的任何内容）。此外，每个员工都有一套与其相关的技能和服务，这些技能和服务在网点之间也不会统一。
这种方法会导致问题吗？在网点层面，这可能不会产生任何影响，但我担心，如果家长小组要求报告，结果可能会产生误导，因为可能会返回 5 名工作人员，实际上他们是同一个人，但可能会被退回。有不同的细节。
I need to store information for staff. Each database instance is per parent company with multiple outlets underneath. Some of the staff that work at an outlet can potentially also work at other outlets, however as each outlet is for the most part autonomous, each outlet will not want other outlets to see their staff list.
I wanted to create unique staff instances and just relate them to the outlets that employ them, keeping their details uniform across the database. However my colleague wishes to allow each outlet to create their own staff members. The consequence of this approach is that John Smith might be a staff member at outlet A, Jonathan smith at outlet B and J smith at outlet C (as each outlet could enter pretty much whatever they want). Also each staff member has a set of skills and services associated with them, which also will not be uniform between outlets.
Will this approach cause problems down the line? At the outlet level it probably won't make any difference, but I am concerned that if the parent group ask for reports, the results may be misleading as perhaps 5 staff members might be returned, which in reality are the same person, however may have different details.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
如果我理解正确的话，您所描述的是在对每个渠道的个人记录进行非规范化（为每个渠道提供完全独立的 John Smith 副本）与定义单个 John Smith 然后定义可能属于的相关表之间进行选择到出口级别的访问。
如果您有选择（以任何一种方式设计系统的自由），则只有 1 个 John Smith + 必要时带有插座特定详细信息的辅助表的标准化方式才是正确的方式。我犹豫是否要说“正确”，但在没有大量用户的情况下，我想说这里的非规范化只会导致可避免的完整性错误。
如果您现在选择非规范化，并且不将出口 A 的约翰·史密斯 (John Smith) 与出口 B 的约翰·史密斯 (John Smith) 联系起来，即使他们实际上是同一个人，那么您都会打开通向不合逻辑数据的大门（更新一个约翰而不是两个约翰）并丢失能够执行简单的操作，例如计算数据库中的不同人数。
如果现在无法识别独特的人，您将来就无法正确地将其他实体与某人联系起来。这至少会使您的查询变得复杂，并且在许多情况下会产生逻辑上不正确但技术上正确的信息。
What you are describing, if I understand you correctly, is choosing between the prospect of denormalizing a person record per outlet (giving each outlet a completely independent copy of John Smith) vs. defining a single John Smith and then defining related tables that could belong to the outlet level of access.
If you have a choice (the freedom to design the system either way) the normalized way with only 1 John Smith + auxiliary tables with outlet-specific details when necessary is the correct way. I hesitate to say 'correct', but in the absence of very large numbers of users I would say denormalization here would only lead to avoidable integrity errors.
If you choose to denormalize now and not relate John Smith at outlet A to John Smith at outlet B even though they are in reality the same person you are both opening the door to illogical data (updates to one John and not both) and losing the ability to do simple things like count the distinct number of people in the database.
Failing to identify unique people now will prevent you from being able to properly relate other entities to a person in the future. This will complicate your queries at the very least and give rise to logically incorrect but technically correct information in many cases.
除了有用的答案（实际回答问题）之外，这里还有一些有用的信息：
如果这些人是工作人员（即他们有工作），那么为什么不使用他们的社会安全号码/国民保险号码作为唯一标识符？这保证是独一无二的，并且他们每个人都保证拥有一个。
编辑：美国社会安全号码保证是唯一的并且不会重复使用。请参阅此处的问题 20：http://www.ssa.gov/history/hfaq.html
编辑：不，他们不是！噢！感谢 Mitch Wheat 提供此链接：http://www.dailyfinance.com/2010/08/12/your-social-security-number-may-not-be-unique-to-you/ 我猜任务是那么这是不可能的，因为没有真正的方法来解决问题......
In addition to the helpful answers (that actually answer the question), here is some useful info:
If these people are staff members (i.e. they have jobs), then why not use their social security number/national insurance number as the unique identifier? That's guaranteed to be unique and they are each guaranteed to have one.
EDIT: US Social Security numbers are guaranteed to be unique and are not reused. See Q20 here: http://www.ssa.gov/history/hfaq.html
EDIT: No they're not! D'oh! Thanks to Mitch Wheat for this link: http://www.dailyfinance.com/2010/08/12/your-social-security-number-may-not-be-unique-to-you/ I guess the task is an impossible one then as there is no real way of solving the problem...
如果网点无法知道其他网点的员工名单，并且网点可以添加员工，则似乎没有任何方法可以阻止两个不同的网点添加同一个人，但他们在系统中被记录为不同的人员。因此，除非有一些中央票据交换所，或者有一种方法可以让一个渠道查看一个人是否已经被列为工作人员（没有获得与她相关的渠道的任何详细信息），我认为你被困住了。
If outlets can't know the stafflist of other outlets, and outlets can add staff, there doesn't seem to be any way of preventing 2 different outlets from adding the same person, but their being recorded as different people in your system. So unless there is some central clearinghouse, or a way for an outlet to see if a person is already listed as a staffmember (w/o getting any details about the outlet(s) she is associated with), I think you're stuck.