关系数据库设计-关系问题
我需要创建数据库表来存储备忘录。在“收件人”子句中，用户可以选择单个员工或员工组（数据库中已经存在与员工具有多对多关系的不同组）。我想知道表格的结构应该是什么。 对于没有组的简单备忘录，我将有“MemoMasters”和“Memodetails”，其中 memodetail 将 EmployeeID 作为外键。我怎样才能在这个结构中嵌入组
问候
I need to create database tables to store memo. in the "To" clause user may select individual employee or group of employees (Different groups are already available in database having many to many relationship with employee). i am wondering what should be structure of tables.
For simple memo where there are no groups i will have "MemoMasters" and "Memodetails" with memodetail having EmployeeID as foreign key. how could i embed groups in this structure
Regards
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
在这种结构中你不能。
这是一个多对多关系，对其进行建模。
编辑（回顾场景）：
上述要求不足以确定“精确解决方案”
冒着过度简化的风险，您应该：
1）检查是否可以将新信息粘贴到任何现有表/实体中
2）如果没有，则检查是否向任何现有表/实体添加属性/列
3) 如果没有，则添加一个新表
决策永远不会明确，您应该考虑几种情况及其后果。您还应该定期检查您的模型，以寻找对其进行标准化的机会，因为它将极大地影响系统的整体功能。
因此，例如，当您声明您已经有一个多对多的组表时，现在的主要问题是是否重用该表或创建一个新表。
这取决于此表中的其他属性。也许对于您的业务模型/用例来说这是有意义的，也许没有。
也许您需要对一个名为“MemoRecipients”的表进行建模，该表将存储有关收件人的信息（递送、确认等）。然后你应该问自己是否需要所有这些状态的时间等等......
只有你才能确定什么是适合你的问题空间的正确设计决策。
In this structure you could not.
This is a many to many relationship, model it as such.
EDIT (reviewing the scenario):
The above requirements are not enough to determine the 'exact solution'
At the risk of overly simplifying, you should:
1) check if you can stick the new information into any existing tables/entities
2) if not, then check if adding attributes/columns to any of the existing tables/entties
3) if not, then add a new table
The decisions are never clearly cut and you should consider several scenarios and their consequences. You should also periodically review your model as it envolves to look for opportunities to normalize it as it will greatly impact overall functionality of your system.
So, for example, as you state that you already have a many to many table for groups the main question now is weather to reuse that table or create a new one.
This depends on the other attributes that are in this table. Maybe for your business model/use case it makes sense, maybe it does not.
Maybe you need to model a table called as "MemoRecipients" which will store info on recipients (delivery, acknowledgement, etc...). Then you should ask yourself do you need the times for all these statuses, etc, etc...
Only you can determine what is a proper design decision for your problem space.
似乎您需要继承层次结构才能实现您所描述的目标。请考虑下图：
在此图中，您将创建一个父表（我称之为海报），员工表和组表是子表。您必须为员工表和组表指定相同的主键，即从组到发帖者以及从员工到发帖者存在一对一的链接。
这样，memo的poster_id字段就指向了超类。您可以在海报、组和员工之间执行联接，以确定您引用的是员工还是组。
我从图中省略了员工和团队之间的关系，正如您所说的那样。
请注意，这只是可能发生的情况的说明，您的解决方案可能会有所不同。
It seems as if you need an inheritance heirarchy to achieve what you describe. Consider the diagram below:
In this diagram you create a parent table (I call it Poster) and the employee and group tables are children. You have to give the employee table and the group table the same primary key, that is, there are one to one links from group to poster and employee to poster.
This way, the poster_id field of memo is pointing to the super class. You can perform a join between poster, group and employee to determine whether you are refering to an employee or a group.
I left out the relationship between employee and group from the diagram as you said you have that already.
Note that this is just an illustration of what is possible, you solution may vary.
只要您将其视为关系建模任务，我就看不到其复杂性。假设您想要一个纯粹的关系解决方案，请尝试这个备忘录数据模型。如果您不熟悉关系数据库建模标准，IDEF1X 表示法 可能会有所帮助。
它使用普通的超类型-子类型结构。
在本例中，它是独占的：MemoAddress，即或者和EmployeeAddress或 GroupAddress。
独占子类型需要超类型中的鉴别器：我使用了布尔值
IsEmployee，CHAR 代码或类似代码也很常见。这是纯粹的5NF；无更新异常；完整的声明性引用完整性。
Ack 位于正确的位置（我使用了 DateTime，但布尔值也很好）。
但是，如果您不习惯的话，填充超类型-子类型结构的投影可能看起来很难看；只是询问您是否需要帮助。
不要区别对待“一名员工的简单备忘录”，你会遇到问题、重复的代码等。
不要
一个单独的问题是，您可能需要撰写
备忘录的Employee的EmployeeId>，这是另一回事，为此您确实需要Memo中的Employee的 FK。As long as you approach it as a Relational modelling task, I don't see the complexity. Assuming you want a pure Relational solution, try this Memo Data Model. If you are unfamiliar with the Standard for modelling Relational databases, the IDEF1X Notation may be helpful.
It uses an ordinary Supertype-Subtype Structure.
In this case it is Exclusive: the MemoAddress ie either and an EmployeeAddress or a GroupAddress.
Exclusive Subtypes need a Discriminator in the Supertype: I have used a boolean
IsEmployee, a CHAR code or similar is also common.This is pure 5NF; no Update Anomalies; full Declarative Referential Integrity.
Ack is in the right place (I've used a DateTime, but boolean is fine as well).
The projection to populate Supertype-Subtype structure might look ugly, though, if you are not used to it; just ask if you need help.
Do not treat "simple memos with one employee" differently, you will run into problems, duplicated code, etc.
A separate issue is, you may need the
EmployeeIdof theEmployeewho wrote theMemo, which is a different thing, and for that you do need an FK forEmployeeinMemo.