如何对此建模？等效 SQL -- 用户、订阅、期刊
具有三个实体 -
- 用户 - 具有用户名/密码、联系信息、账单信息等。
- 期刊 - 具有 periodical_name、类别、publisher_info、print_cycle、unit_price 等。
- 订阅 - 具有用户 ID、期刊 ID 、订阅开始/结束日期、状态等。
以及以下关系 -
- 用户可以订阅多个期刊。
- 订阅属于用户，并且具有关联的期刊。
- 期刊有许多与其关联的订阅。
st，
- 用户 - 订阅关系是一对多，
- 周期 - 订阅关系是一对多，
我的问题 -
此模型描述对于通常发现的现实世界关系是否正确？
或者，我是否最好将期刊折叠为订阅，特别是如果每个期刊的信息并不广泛并且可以编码到分隔符单独的文本字段中（例如“PeriodicalName:Frequency:Publisher:UnitPrice”）？
我可以说通过关联性用户-周期关系是多对多吗？
如果有人可以展示如何在用户或订阅表周围设置约束（假设我不折叠订阅和期刊），当用户因某种原因需要删除时，所有关联的订阅都会受到限制也被删除。
想要将用户记录保留一段时间（比如一年），即使在所有订阅都已过期之后，所以我想我可以将 NULL 分配给用户表中的 FK subscription_id，对吧？这是当订阅表中不存在相应记录时。
Got three entities -
- User - Has username/password, contact information, billing information etc.
- Periodical - Has periodical_name, category, publisher_info, print_cycle, unit_price etc.
- Subscription - Has the user-id, periodical-id, subscription start/end date, status etc.
And following relationships -
- User can have Subscription for multiple Periodicals.
- Subscription belongs-to a User, and has-a Periodical associated.
- Periodical has-many Subscriptions associated with it.
s.t.,
- User--Subscription relation is One-to-Many,
- Periodical--Subscription relation is One-to-Many,
My questions --
Is this model description correct for the real-world relationships typically found ?
Or, am I better off, collapsing Periodical into Subscription, especially if per Periodical information is not extensive and can be encoded into, say a delimiter separate text field (e.g. "PeriodicalName:Frequency:Publisher:UnitPrice") ?
Can I say that via associativity User--Periodical relation is Many-to-Many ?
Would appreciate if someone can show how I could put a constraint around either User or Subscription table (assuming that I don't collapse Subscription & Periodical), s.t. when a User needs to be removed for some reason, all associated Subscriptions are deleted too.
Would like to keep a User record around for sometime (say a year), even after all Subscriptions have expired, so I guess I can assign NULL to the FK subscription_id in User table, right ? This is when no corresponding record exists in Subscription table.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
是的。
您正在描述“非规范化”，在极少数情况下可能有用，但它违反了关系设计。
您
是的。
这种类型的约束（一对一）在大多数 RDBMS 产品中并未以声明方式实现。相反，我们有 1 到 0 或 1。您可以使用触发器来做到这一点，但它很棘手且微妙。 Google 的对象角色建模是一种更全面的建模技术，可以解决类似的问题（以及“非此即彼”和“至少 2”以及许多其他内容。）
这将是处理它的标准方法。您还可以为订阅保留到期日期。但是 1 到 0 或 1 的缺陷很好地促进了你的建议。
Yes.
You are describing "denormalization" which may under rare circumstances be useful but it's a violation of relational design.
yes.
This type of constraint (1-to-1) is not implemented declaratively in most RDBMS products. Instead we have 1-to-zero-or-one. You can do it with triggers but it's tricky and subtle. Google for Object-Role Modeling which is a more comprehensive modeling technique that addresses stuff like this (and "either-or" and "at least 2" and many others.)
That would be the standard way to handle it. You could also leave the subscription with an expiration date. But the 1-to-zero-or-one deficiency nicely facilitates your suggestion.
您的模型是正确的。我会将其实现为 3 个表“用户”、“期刊”、“订阅”。
您不应将期刊折叠到订阅中。如果期刊或期刊的某些部分对于所有用户来说都是相同的，那么它就会出现在该表中。订阅发生的任何更改都会记录在订阅表中。
是的。
我认为这是一个坏主意。您在这里谈论的是业务逻辑。这不应该在数据库中实现。您的要求可能会改变。您可以要求订阅表中的所有用户 (FK) 都存在 - 但当用户被删除时，您不应该让数据层删除订阅 - 相反，它会阻止并表示无法删除，因为 FK 正在被使用在订阅表中。 （您的逻辑层需要先删除所有订阅，然后才能删除用户。）
订阅的用户表中不需要 FK。订阅表包含用户和期刊的 FK——这就是所谓的连接表。当您想要从用户表中检索用户数据和订阅信息时，您将需要联接。通常您可能不需要这个（例如，您知道用户 ID，而您只想列出该用户的订阅信息）。
You model is correct. I would implement this as 3 tables User, Periodical, Subscription.
You should not collapse Periodical into Subscription. If a Periodical or some portion of the periodical is the same for all users it goes in that table. Anything that changes by subscription goes in the subscription table.
Yes.
I think this is a bad idea. You are talking about Business logic here. That should not be implemented in the database. Your requirements might change. You can require that all users (FKs) in the subscription table exist -- but you should not have the data tier remove subscriptions when a user is deleted -- instead it will block and say it can't remove because the FK is being used in the subscription table. (Your logic layer will need to delete all subscriptions before it can delete the user.)
You don't need a FK in the user table for subscriptions. The subscription table contains the FK for both user and periodical -- it is what is called a join table. You would need a join when you want to retrieve both user data from the user table and the subscription information. Often you might not need this (for example you know the user id and you just want to list that user's subscription information).