列依赖于其他列值

发布于 2024-12-29 21:58:21 字数 339 浏览 1 评论 0原文

我有一列将具有三个可能的值（稍后可能会更多），我将其存储为枚举：

yes immediately
yes later
no

如果选择稍后，我们还需要存储日期该事件应该发生。在我看来，这必须是另一篇专栏。

我的问题是设计缺乏完整性。即使枚举不是 yes later，也没有什么可以阻止 date 具有值，如果是的话，也没有任何东西可以强制执行 date 。

能否改进这种设计，使两根柱子之间保持完整性？使用单独的表和不同的列类型绝对是可能的。

原文

I have a column that will have three possible value (possibly more later) that I'm storing as an enum:

yes immediately
yes later
no

If the choice of yes later is made, we also need to store the date that the event should occur. It seems to me that this has to be another column.

My problem with this is that the design lacks integrity. There's nothing to prevent date from having a value even if the enum is not yes later, nor is there anything to enforce a date if it is.

Can this design be improved so that there is an integrity between the two columns? Using separate tables and different column types is definitely a possibility.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

很糊涂小朋友 2025-01-05 21:58:22

您可以通过添加子类型表来解决此问题（我也更喜欢查找表而不是枚举，甚至更多，因为您希望灵活并稍后添加更多类型）：

ChoiceType
----------            --- the lookup table
Choice      
Desciption
PRIMARY KEY(Choice)

Event
-----                 --- your table
EventId 
Choice 
... other stuff for all events
PRIMARY KEY(EventId)
FOREIGN KEY (Choice)
  REFERENCES ChoiceType(Choice)

EventYL 
-------               --- the YesLater table
EventId 
EventDate
... other stuff for YesLater events only
PRIMARY KEY(EventId)
FOREIGN KEY (EventId)
  REFERENCES Event(EventId)

如果您希望为其他选择存储不同的数据，那么您可以添加EventYI 表（用于 Yes Immediate）、EventNO 等。

这种设计的唯一问题是，没有任何东西可以阻止事件发生。不是“稍后再说”添加到 EventYL 表中，因此应以其他方式强制执行。如果只有 MySQL 有检查约束，则该表可以更改为：

EventYL 
-------                --- YesLater version 2
EventId 
Choice
EventDate
PRIMARY KEY(EventId, Choice)
FOREIGN KEY (EventId, Choice)
  REFERENCES Event(EventId, Choice)
CHECK (Choice = 'YL')

You could solve this by adding a subtype table (I would also prefer a lookup table over enum, even more because you want to be flexible and add more types later):

ChoiceType
----------            --- the lookup table
Choice      
Desciption
PRIMARY KEY(Choice)

Event
-----                 --- your table
EventId 
Choice 
... other stuff for all events
PRIMARY KEY(EventId)
FOREIGN KEY (Choice)
  REFERENCES ChoiceType(Choice)

EventYL 
-------               --- the YesLater table
EventId 
EventDate
... other stuff for YesLater events only
PRIMARY KEY(EventId)
FOREIGN KEY (EventId)
  REFERENCES Event(EventId)

If you want different data stored for the other choices, then you can add an EventYI table (for Yes Immediate), an EventNO, etc.

The only problem with this design, is that nothing prevents an event that is not 'Yes Later' to be added in the EventYL table, so this should be enforced otherwise. If only MySQL had check constraints, the table could be changed into:

EventYL 
-------                --- YesLater version 2
EventId 
Choice
EventDate
PRIMARY KEY(EventId, Choice)
FOREIGN KEY (EventId, Choice)
  REFERENCES Event(EventId, Choice)
CHECK (Choice = 'YL')

回复收藏 0 原文