设计此数据库方案的最佳方法？ [英] Best way to design this database scenario?
问题描述
要求是存储不同实体类型的附件。
假设我们有3种实体类型，分别是Company，Department和Employee。每个附件都可以有多个附件（文档）。
哪个是处理此问题的最佳方法？
解决方案1：
公司表
- CompanyId
部门表
- DeptId
员工表
- EmployeeId
AttchmentType表
- TypeId
- 类型（公司，部门，员工）
附件表
- AttachmentId
- TypeId（映射到附件类型）
- entityId（映射到CompanyId / DeptId / EmployeeId）
优点：以后我可以轻松添加新的实体类型
缺点：在在这种情况下，我无法在实体和附件之间保持外键关系。
解决方案2：
公司表
- CompanyId
部门表
- DeptId
Employee table
- EmployeeId
CompanyAttachments表
- 附件编号
- CompanyId（FK）
DeptAttachments表
- AttachmentId
- DeptId（FK）
EmployeeAttachments表
- AttachmentId
- EmployeeId（FK）
优点：外键完整性
缺点：在为了添加新实体，我需要分别拥有新的附件表。
那么假设将来我可能需要添加新实体，这是最好的方法吗？ p>
编辑1：
谢谢
如果我想使用解决方案2，我会看到在附件表中使用新列更容易，而不必为每个实体都创建新的附件表以映射它们？
之类的
公司表
- CompanyId
部门表
- DeptId
员工表
- EmployeeId
附件
- AttachmentId
- CompanyId（FK）
- EmployeeId（FK）
- 部门编号（FK）
我在这里错过了什么吗？
我肯定会选择解决方案2。解决方案1的专业人士并不是真正的专家。如果您添加一个新实体，则必须已经为该实体添加了一个新表，并且已经在添加或更改现有代码来对其进行处理。您应该能够制作一些处理模式的通用对象，从而使重复的代码成为问题。
Requirement is to store attachments for different entity types.
Say we have 3 entity types Company , Department and Employee. Each can have multiple attachments (documents).
Which is the best way to handle this?
Solution 1:
Company table
- CompanyId
Dept table
- DeptId
Employee table
- EmployeeId
AttchmentType table
- TypeId
- Types (company, dept, employee)
Attachments table
- AttachmentId
- TypeId (maps to attachment type)
- entityId (maps to CompanyId / DeptId / EmployeeId)
Pros: I can add new entity types easily in future
Cons: In this case I can't have foreign key relationship maintained between entities and attachments.
Solution 2:
Company table
- CompanyId
Dept table
- DeptId
Employee table
- EmployeeId
CompanyAttachments table
- AttachmentId
- CompanyId (FK)
DeptAttachments table
- AttachmentId
- DeptId (FK)
EmployeeAttachments table
- AttachmentId
- EmployeeId (FK)
Pros: Foreign key integrity
Cons: In order add new entity I need to have new attachment table separately.
So which is the best way to go with assuming I may need to add new entities in future?
Edit 1:
Thanks for your reply guys.
If I want to go with solution 2, I see that creating new columns in attachments table easier instead of creating new attachment tables for every entity just to map them? something like,
Company table
- CompanyId
Dept table
- DeptId
Employee table
- EmployeeId
Attachments
- AttachmentId
- CompanyId (FK)
- EmployeeId (FK)
- DepartmentId (FK)
am I missing something here?
I'd definitely go with solution #2. Your one pro for solution #1 isn't really a pro. If you add a new entity you're going to necessarily have to already add a new table for that entity and you'll already be adding or changing existing code to handle it. You should be able to make some generic objects that handle the pattern so that duplicated code isn't a problem.
这篇关于设计此数据库方案的最佳方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
