基本标准化问题
这可能不完全是一个“标准化”问题，它更多的是我要保存的数据类型。
我刚刚完成了消息和电子邮件系统的规范。我的想法是，我需要保存我的网络服务内部的所有消息，但也知道是否已随该消息发送了电子邮件。
这是规格。
规范
- 任何消息都存储在一个表中。
消息可以来自未注册用户，也可以来自注册用户。
未注册的用户消息将只有一个回复电子邮件地址
注册的用户消息将包含发送者的用户 ID
消息要么由用户拥有（这意味着它们是发送至）或消息是由用户角色共享。
当消息由用户拥有时，我们会记录有关此消息的一些信息（与消息相同的表）。
a) 用户是否打开/阅读了消息？
b) 是_电子邮件_发送给邮件所有者还是只是内部邮件
c) 首次阅读消息的日期
d) 消息发送日期
当消息发送给一组用户时，意味着它们被发送给“所有用户”、“所有所有者”或“所有超级管理员”...
a) 消息会在
messages表中保存一次，并带有发送日期b) 每个单独的开盘都在单独的表中进行跟踪
c) 字段记录是否已发送直接电子邮件_，或者是否仅将其保存在系统内部。 （单独的表格）
消息可以线程化，这意味着如果消息得到响应，则它是子消息或原始消息。
消息具有不同的“类型”，这意味着消息可以是“系统通知”、“查询”、“个人消息”、“私人消息”、“交易信息”
链接到产品查询的消息将保存他们正在查询的产品的 ID。 （即相关属性）。
结束规范
现在是实际问题...
正如您在项目符号 1)(b) 中看到的那样，我正在录制发送给个人用户的消息，如果电子邮件也被发送到发送该消息。
但是，当电子邮件发送给一组用户时，我会记录电子邮件是否发送到完全不同的表中。 显然是因为我无法将这些信息保存在同一个表中。
您对此模型有何看法。我不会复制任何数据，但我会分离数据的保存位置。我应该有一个 email_sent 表来记录所有这些信息吗？
This might not exactly be a "normalization" question, it's more the type of data which I am saving.
I've just done a specification for a messaging and email system . The idea is that I need to save all of the messages which are internal to my web service, but also know if an email has been sent with that message.
Here is the specification.
Specification
- Any messages are stored in one table.
Messages can be from unregistered users, or registered users.
An unregistered user message will just have a return email address
A registered user message will have the user id of the sender
Messages are either owned by a User (meaning that they are the sent to) or messages are shared by user roles.
When a message is owned by a user, we record some information about this message (same table as the message).
a) Has the user opened/read the message?
b) Was an _email sent_ to the owner of the message or is it just an internal message
c) Date the message was first read
d) Date the message was sent
When a message is sent to a group of users, meaning that they are sent to "All Users", or "All Owners" or "All SuperAdmin"...
a) The message is saved once in the
messagestable with a sent dateb) Each individual open is tracked in a seperate table
c) A field records if a direct _email has been sent_, or if it is just saved internally in the system. (seperate table)
Messages can be threaded, this means that if a message is responded to, that it is a child or the original message.
Messages have different "Types", meaning that a message can be "System Notice", "Enquiry", "Personal Message", "Private Message", "Transactional Information"
Messages which are linked to an enquiry for a product, will save the ID of the product they are enquiring for. (ie The relevant property).
End Specification
Now the actual question...
As you can see in bullet 1)(b) I am recording for a message which is sent to an indiviual user, if an email was also sent for that message.
However, when an email is sent to a group of users, I am then recording whether an email was sent in a completely different table.
Obviously because I can't save this information in the same table.
What are your opinions on this model. I'm not duplicating any data, but I'm seperating where the data is saved. Should I just have a email_sent table to record all of this information.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
很难说你现在的设计是好是坏。从表面上看，我认为将同一条信息分成两个地方是错误的。在表格中添加与个人更接近的发送的个人电子邮件的注释以及发送给与群组更接近的群组的电子邮件的注释似乎更容易。但是，您的代码必须在两个地方查找才能查找有关任何电子邮件或一般所有电子邮件的信息。
如果标志
email_sent的含义对于单个用户和一组用户的成员来说是相同的，那么始终在两个地方查找本质上是一种信息的内容将会很乏味（从代码角度来说，这可以归结为可能很慢并且难以支持）。另一方面，
email_sent可能对您的事务或报告逻辑并不重要，而只是一个“顺便凑合”的稍微有趣的事实。在这种情况下，尝试将两个不同的email_sent标志强制放入一个位置可能需要将两个实体进行不方便且不可取的混搭，这两个实体由于所有其他更重要的属性而应该是不同的。如果没有更好地了解您的业务需求，很难给出结论性的答案，但这是您必须考虑的权衡。
It is hard to say whether your current design is good or bad. On the surface, I think that it is a mistake to separate the same piece of information into two places. It may seem easier to have a note about an individual email sent in the table which is closer to the individual and notes about emails sent to groups closer to the groups. However, your code is going to have to go looking in two places to find information about any email or about all emails in general.
If the meaning of the flag
email_sentis the same for an individual user as it is for a member of a group of users, then looking in two places all the time for what is essentially one kind of information will be tedious (which in code terms comes down to being potentially slow and hard to support).On the other hand, it may be that
email_sentis something that is not important to your transactional or reporting logic and is just a mildly interesting fact that is "coming along for the ride". In this case, trying to force two differentemail_sentflags into one place may require an inconvenient and inadvisable mash-up of two entities that ought to be distinct because of all of their other, more important attributes.It is difficult to give a conclusive answer without having a better understanding of your business requirement, but this is the trade-off you have to consider.
创建 3 个表：
带有 id（自动键）、msgtext、类型（值 U 或 R）、userId/roleId 的 MSG
包含角色 ID、用户 ID 的角色
包含用户 ID、MsgId、打开日期、读取日期等的 ACCS
MSG 记录消息，并使用一种类型来查看消息是否来自角色或未注册用户
ROLES 将一个角色指向多个用户
ACCS 记录用户的所有内容，无论是否注册。
要检索，请将 MSG 类型 U 与 ACCS 结合，
将 MSG 类型 R 与 ROLES 结合，然后与 ACCS 结合。
要检索全部，请将它们联合起来
Create 3 tables:
MSG with id (key auto), msgtext, type (value U or R), userId/roleId
ROLES with roleId, userId
ACCS with userId, MsgId, date opened, read, etc
MSG records the message, with a type to see if it's from a role or unregistered user
ROLES points one role to many users
ACCS records everything, for a user, registered or not.
To retrieve, join the MSG type U with ACCS
join MSG type R with ROLES and then with ACCS
To retrieve all, UNION them