3NF Normalization tables from Domain Model

Question

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?

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:

Alert: AlertId (PK), AlertType, ObservationId

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

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

Appreciate any help.

Answer 1

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.