自引用实体 - 多对多
就在关于此数据库设计情况的一些建议之后。
所以我的数据库中有两个表。
表 1：患者 表 2：索赔人
患者持有私人患者的属性数据，因此有关该人的详细信息、他/她的生日、姓名、医疗状况等。 索赔人是代表患者付款的实体，因此索赔人可以是患者本人、他人、企业（支付工伤费用）、私人医疗保健提供者、政府机构等。
患者和索赔人都拥有以下身份证明：其他表（例如发票、收据等）中的外键...
一名患者可以有多个索赔人（多个实体可以代表他付款），每个索赔人可以有多个患者。
经过进一步调查，我发现患者和索赔人的许多属性是重叠的，因为患者可以自己支付费用，因此是私人索赔人。
我的想法是将两个表合并为一个，简单地将其称为帐户，并有一个 ClaimantType 字段来标识帐户的类型，无论是私人、医疗保健、企业还是政府。
对于此更改，我需要牢记哪些潜在的实际缺点？除了更改数据库中的其他链接表之外？
编辑：为了明确起见，已经有一个连接表 PatientClaimants ，它基本上只是将患者映射到索赔人。谢谢！
Just after some advice regarding this database design situation.
So I have two tables in the database.
Table 1: Patients
Table 2: Claimants
Patients holds attributes data on a private patient, so details about the person, his/her birthday, names, medical conditions, etc etc..
Claimants is the entity who pays on behalf of the patient, so Claimant can be a patient himself, another person, a business (who pays for work injury), private healthcare provider, government bodies etc etc..
Patients and claimants have IDs who are foreign keys in other tables such as invoices, receipts etc...
One Patient can have multiple Claimants (More than one entity can pay on his behalf), each Claimant can have multiple patients.
On further investigation, I've identified that many of the attributes of Patients and Claimants overlap as a Patient can pay for himself thus is a private Claimant.
My thinking is to merge the two tables into one and simply call it accounts and have a claimantType field to identify the type of the account, be it private, healthcare, business or government.
What potential practical drawbacks do I need to keep in mind with this change? Apart from the changing the other linked tables in database?
EDIT: Just to make it clear, There's already a junctional table PatientClaimants which basically just map the patients to the claimants. Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我认为合并这两个表是错误。
病人始终是人。因此它不可能是一个企业或一个组织。
我相信这里你有：
这里 PersonId 或 BusinessId 其中之一可以为空。
Merging these two tables I believe is wrong.
A patient is always a human. So it cannot be a business or an organisation.
I believe here you have:
Here PersonId or BusinessId one of them can be null.
您可以 (a) 放入一个将患者 ID 与索赔人 ID 相关联的交叉表，或者 (b) 正如您所讨论的，将它们合并在一起 - 但如果您已经有数据，则可能会出现问题。
您还可以设置一个人口统计表，显示患者和索赔人之间的常见数据，并引用缩写的患者/索赔人表 - 这样您就不会破坏现有的结构。
You could either (a) put in an intersection table that relates patient IDs to claimant IDs, or (b) as you discussed, merge them together - but if you already have data out there that could be problematic.
You could also set up a demographics table that shows common data between patients and claimants and references an abbreviated patient/claimant table - this way you do not break your existing structure.