问题描述
希望获得有关如何在数据库表中进行表示的帮助(第三标准格式).我觉得我做的不正确.我了解，通过一对多关联，应该将"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规范化表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！