正在使用 1:n +同一张表之间的 n:1 设计不好？
想象一下表 1“事件”包含事件信息，但您希望让用户有机会将多个日期中的一个日期标记为选定的（因为对于哪个日期是正确的有多种意见）。因此日期实际上会存储在表 2“Event”中。
当然，“事件”表与“日期”表之间需要存在：1 关系，因此每个事件可以有多个日期。但是，添加从“事件”到“日期”的 1:n 关系来存储该“事件”记录当前选择的日期是否是一种好的做法？
显然，另一种选择是在表“日期”（“选定”）中存储一个标志，但我认为这对于“事件”表的读取访问速度不会那么快。特别是在 LINQ2SQL 中，如果存在 1:n 关系，那么访问日期信息将非常容易。
（由于维护原因，我不想在“事件”表中额外存储实际的“选定”日期值 - 如果选择了不同的“首选”日期并且大约有 6 个日期字段，则必须手动处理它处理各种日期信息）
Imagine a table 1 "Event" contains event-information but you want to give users the opportunity to mark one date out of several dates as selected (because there are several opinions of which date is correct). So the dates would actually be stored in table 2 "Event".
Of course there needs to be a n:1 relationship between the tables "Event" to "Date" so multiple dates per event are possible. But is it good practices to also add an 1:n relationship from "Event" to "Date" to store the currently selected date for that "Event"-record?
An alternative would obviously be to store a flag in table "Date" ("Selected"), but I think that would be not as fast for read access on the "Event" table. Especially in LINQ2SQL it would be really easy to access the date information if there is a 1:n relationship.
(I don't want to additionally store the actual "selected" date values in the "Event" table because of the maintenance - You have to handle it manually if a different "prefered" date is selected and there are about 6 date fields to handle all kinds of date-information)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
（首先，我当然希望您的表不被命名为
Table1和Table2。如果可能的话，请尝试具体说明问题中指定的表 ）不，这不是一个坏主意，您的需求可以轻松地仅用主键和外键来表示。这是一个经常遇到的问题，其中父表和子表之间存在 1:n 关系，但要求您有一个具有特定属性的子表。例如，许多 CRM 系统允许每个客户使用多个地址，但只能将一个地址定义为主要地址。
最简单的场景是这样的：
然后，一旦这些表存在，向
Event添加一个可为空的SelectedDate列，并对Event进行外键约束使用EventID和SelectedDate引用EventDate(Firstly, I certainly hope your tables are not named
Table1andTable2. Try to be specific with the table named in your question, if possible)No, this is not a bad idea, and your requirements can be easily represented with only primary and foreign keys. This is a problem that's encountered quite often, where you have a 1:n relationship between a parent and child table, but there's a requirement that you have a single child that has a particular attribute. For instance, many CRM systems allow multiple addresses per customer, but only one address can be defined as the primary address.
The simplest scenario is this:
Then, once those tables exist, add a nullable
SelectedDatecolumn toEvent, and make a foreign key constraint onEventthat referencesEventDateusingEventIDandSelectedDate是的，这是糟糕的设计。您应该有一个事件表、一个日期表以及它们之间的 am:n 映射表。
Yes, it is bad design. You should have a table of events, and a table of dates, and a m:n mapping table between them instead.
您可以（也可以不）对
UserID、EventID设置唯一约束在UserSchedule表中。You may (or may not) place a unique constraint on
UserID, EventIDin theUserScheduletable.就数据模型而言，如果它是对您试图表示的现实的准确描述，那么它并不是“坏”或不正确的。不幸的是，SQL DBMS 在执行此类循环约束方面遇到了困难。为了在 SQL 中完成它，您通常必须做出妥协 - 通常通过创建一个额外的表来表示事件和日期之间的关系。
As far as a data model goes it is not "bad" or incorrect if it's an accurate description of the reality you are trying to represent. Unfortunately SQL DBMSs have trouble enforcing cyclical constraints of this kind. To get it done in SQL you will usually have to compromise - typically by creating an extra table to represent the relationship between Event and Date.