域模型的3NF规范化表 [英] 3NF Normalization tables from Domain Model

问题描述

希望获得有关如何在数据库表中进行表示的帮助(第三标准格式).我觉得我做的不正确.我了解，通过一对多关联，应该将"1"表中的主键(在本例中为Alert)包括在其子类中，但是我不确定文本响应和文本的主键应该是什么.VoiceResponse?

Hoping to get some assistance on how this would be represented in a database table (3rd normal form). I feel what I have done is incorrect. I understand that with a one to many association, the primary key from the "1" table (in this instance Alert) should be included in it's child classes however I am unsure what the Primary Key should be for Text Response & VoiceResponse?

我所拥有的东西看起来不正确，但是我确实知道每个对象都需要一个uniqueId.注意:observationId属性是从图片中切出的1到许多类.

What I have does not look correct but I do know that each object requires a uniqueId. Note: The ObservationId attribute is from a 1 to many class that has been cut out of the pic.

到目前为止，我的表是:

The tables I have so far are:

警告:AlertId(PK)，AlertType， ObservationId

Alert: AlertId (PK), AlertType, ObservationId

TextResponse:TextResponseId(PK)， AlertID(FK)，TextResponse

TextResponse: TextResponseId (PK), AlertID (FK), TextResponse

语音响应:VoiceResponseId(PK)， AlertId(FK)，SoundClip

VoiceResponse: VoiceResponseId (PK), AlertId (FK), SoundClip

感谢任何帮助.

推荐答案

我在这里看到了可能的依赖关系，也许您忽略了它.仅当 AlertId 与文本/声音内容之间没有任何关系时，才可以对您当前的设计进行规范化.但是，实际上，您可能会看到同一警报多次发生.在当前设计下，您将多次存储相同的文本/语音内容.要解决此问题，您可以通过将两个响应表更改为纯桥表来重构:

I can see a possible dependency here which perhaps you overlooked. Your current design would only be normalized if there were no relationship between an AlertId and the text/sound content. But, in practice, you would probably see the same alerts happening multiple times. Under your current design, you would be storing the same text/voice content multiple times. To fix this, you can refactor by changing the two response tables into pure bridge tables:

TextAlert:  TextResponseId,  AlertID (primary key is both columns)
VoiceAlert: VoiceResponseId, AlertId (PK both columns)

然后，创建两个新表来存储实际的文本/语音内容:

Then, create two new tables to store the actual text/voice content:

TextResponse:  TextResponseId  (PK), TextResponse
VoiceResponse: VoiceResponseId (PK), SoundClip

现在，您的设计对于出现多次的相同警报内容具有鲁棒性.

Now your design is robust to the same alert content appearing more than once.

我可以提出的另一条评论是，在实践中，您可能只希望有一个响应表，该表可以存储文本或声音，也可以存储为斑点.

Another comment I can make is that in practice you might want to have just one response table, which can store either text or sounds, perhaps as a blob.

这篇关于域模型的3NF规范化表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！