数据库表上的 1 对 1 关系有味道吗？
我有一个有很多字段的表。 这些字段可以分为逻辑组 - 例如工作的项目经理信息。 这些分组本身并不是真正的实体候选者，因为它们没有也不应该有自己的 PK。
目前，为了对它们进行分组，字段具有前缀（例如 PmFirstName），但我正在考虑将它们分成多个表，在主表上具有 1:1 关系。
我这样做时有什么需要注意的吗？ 这只是一个糟糕的选择吗？
我可以看到，由于所有额外的连接，我的查询可能会变得更加复杂，但这可以通过视图来缓解，对吗？ 如果我们讨论的表的记录少于 100k，这会对性能产生明显影响吗？
编辑：我将进一步证明非实体候选人的想法。 该信息由我们的用户群输入。 他们彼此不了解/不关心。 因此，同一用户可能会提交相同的“projectManager 名称”或任何此时不会违反任何约束的内容。 如果我们想要关联来自不同用户的条目，则由我们稍后决定。 如果我给这些东西自己的密钥，它们将以与主表相同的速度增长 - 因为它们本质上是同一实体的一部分。 用户绝不会从可用的“项目经理”列表中进行选择。
因此，鉴于上述情况，我认为它们不是实体。 但也许不是——如果您有进一步的想法，请留言。
I have a table that has a bunch of fields. The fields can be broken into logical groups - like a job's project manager info. The groupings themselves aren't really entity candidates as they don't and shouldn't have their own PKs.
For now, to group them, the fields have prefixes (PmFirstName for example) but I'm considering breaking them out into multiple tables with 1:1 relations on the main table.
Is there anything I should watch out for when I do this? Is this just a poor choice?
I can see that maybe my queries will get more complicated with all the extra joins but that can be mitigated with views right? If we're talking about a table with less than 100k records is this going to have a noticeable effect on performance?
Edit: I'll justify the non-entity candidate thoughts a little further. This information is entered by our user base. They don't know/care about each other. So its possible that the same user will submit the same "projectManager name" or whatever which, at this point, wouldn't be violating any constraint. Its for us to determine later on down the pipeline if we wanna correlate entries from separate users. If I were to give these things their own key they would grow at the same rate the main table grows - since they are essentially part of the same entity. At no pt is a user picking from a list of available "project managers".
So, given the above, I don't think they are entities. But maybe not - if you have further thoughts please post.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
对我来说，除非对于某些行或查询，您对额外的列不感兴趣，否则它们会闻起来。 例如，如果对于大部分查询，您没有选择 PmFirstName 列，或者如果对于大部分行子集，这些列为 NULL。
我喜欢气味标签。
To me, they smell unless for some rows or queries you won't be interested in the extra columns. e.g. if for a large portion of your queries you are not selecting the PmFirstName columns, or if for a large subset of rows those columns are NULL.
I like the smells tag.
我使用一对一的关系来构建类似继承的结构。
例如，所有债券都有一些基本信息，如 CUSIP、Coupon、DatedDate 和 MaturityDate。 这一切都在主表中。
现在，每种类型的债券（国债、公司债券、市政债券、代理债券等）也都有其独特的一组栏目。
在过去，我们只有一张非常宽的桌子来包含所有这些信息。 现在，我们将特定于类型的信息分解为单独的表，这为我们提供了更好的性能。
创建一个person表，每个数据库都需要这个。 然后在您的项目表中有一个名为 PMKey 的列，它指向人员表。
I use 1 to 1 relationships for inheritance-like constructs.
For example, all bonds have some basic information like CUSIP, Coupon, DatedDate, and MaturityDate. This all goes in the main table.
Now each type of bond (Treasury, Corporate, Muni, Agency, etc.) also has its own set of columns unique to it.
In the past we would just have one incredibly wide table with all that information. Now we break out the type-specific info into separate tables, which gives us much better performance.
Create a person table, every database needs this. Then in your project table have a column called PMKey which points to the person table.
为什么你觉得这组字段不是一个实体候选者？ 如果不是，那为什么要尝试用前缀来识别它们呢？
删除前缀或将它们提取到自己的表中。
Why do you feel that the group of fields are not an entity candidates? If they are not then why try to identify them with a prefix?
Either drop the prefixes or extract them into their own table.
如果它们是可以在其他地方使用的单独的逻辑实体，那么将它们分成单独的表是有价值的。
因此，当前“项目经理”可能与所有项目都是 1:1，但稍后您可能希望能够让一名项目经理负责多个项目，这是有道理的。
所以有额外的桌子是很好的。
如果您有 PrimaryFirstName、PrimaryLastName、PrimaryPhone、SecondaryFirstName、SecondaryLastName、SEcondaryPhone
您可以只拥有一个包含 FirstName、LastName、Phone 的“Person”表，
那么您的原始表只需要“PrimaryId”和“SecondaryId”列来替换您的 6 列以前有过。
此外，使用 SQL，您可以跨物理位置拆分文件组和表。
因此，您可以有一个 POST 表和一个 COMMENT 表，它们具有 1:1 关系，但 COMMENT 表位于不同的文件组上，并且位于具有更多内存的不同物理驱动器上。
1:1并不总是有气味。 除非没有任何目的。
It is valuable splitting them up into separate tables if they are separate logical entities that could be used elsewhere.
So a "Project Manager" could be 1:1 with all the projects currently, but it makes sense that later you might want to be able to have a Project Manager have more than one project.
So having the extra table is good.
If you have a PrimaryFirstName,PrimaryLastName,PrimaryPhone, SecondaryFirstName,SecondaryLastName,SEcondaryPhone
You could just have a "Person" table with FirstName, LastName, Phone
Then your original Table only needs "PrimaryId" and "SecondaryId" columns to replace the 6 columns you previously had.
Also, using SQL you can split up filegroups and tables across physical locations.
So you could have a POST table, and a COMMENT Table, that have a 1:1 relationship, but the COMMENT table is located on a different filegroup, and on a different physical drive with more memory.
1:1 does not always smell. Unless it has no purpose.
我通常不使用一对一关系，除非有特定的性能原因。 例如，将不常用的大文本或 BLOB 类型字段存储在单独的表中。
但我怀疑这里还发生了其他事情。 在您给出的示例中 - PmFirstName - 似乎应该有一个与“ProjectManagers”或“Employees”表相关的 pm_id。 您确定这些分组都不是真正的实体候选者吗？
I don't usually use 1 to 1 relations unless there is a specific performance reason for it. For example storing an infrequently used large text or BLOB type field in a separate table.
I would suspect that there is something else going on here though. In the example you give - PmFirstName - it seems like maybe there should be a single pm_id relating to a "ProjectManagers" or "Employees" table. Are you sure none of those groupings are really entity candidates?