使用1：n + n：1之间的相同表设计不好？(Is using 1:n + n:1 between the same tables bad design?)

使用1：n + n：1之间的相同表设计不好？(Is using 1:n + n:1 between the same tables bad design?)

想象一下，表1“事件”包含事件信息，但您希望用户有机会将多个日期中的一个日期标记为已选择（因为有几个日期是正确的意见）。 所以日期实际上会存储在表2“事件”中。

当然，表“事件”到“日期”之间需要有一个关系：因此每个事件可以有多个日期。 但是，从“事件”到“日期”添加1：n关系以存储“事件”记录的当前所选日期是不错的做法？

另一种选择显然是在表“Date”（“Selected”）中存储一个标志，但我认为在“事件”表上读取访问速度不会那么快。 特别是在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)

最满意答案

（ 首先，我当然希望你的表没有命名为Table1和Table2 。如果可能的话，尝试特定于你问题中指定的表 ）

不，这不是一个坏主意，只需使用主键和外键即可轻松表示您的要求。 这是一个经常遇到的问题，在父表和子表之间存在1：n关系，但是要求您有一个具有特定属性的子项。 例如，许多CRM系统允许每个客户使用多个地址，但只能将一个地址定义为主地址。

最简单的方案是：

Event ------------ EventID ...etc. Primary Key (EventID) EventDate ----------- EventID Date Primary Key (EventID, Date) Foreign Key (EventID) references Event (EventID)

然后，一旦存在这些表，就向Event添加一个可空的SelectedDate列，并使用EventID和SelectedDate对Event引用EventDate的外键约束

(Firstly, I certainly hope your tables are not named Table1 and Table2. 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:

Event ------------ EventID ...etc. Primary Key (EventID) EventDate ----------- EventID Date Primary Key (EventID, Date) Foreign Key (EventID) references Event (EventID)

Then, once those tables exist, add a nullable SelectedDate column to Event, and make a foreign key constraint on Event that references EventDate using EventID and SelectedDate