Database Design - Normalization

Question

This is a new topic to me, I've read a few articles and I'm still unclear even to the point where I'm unsure if the following question relates to the title of this post or not.

My system sends data to a user. The user may elect for the data to be sent by:

• XML
• Email
• Post

Depending on what the user chooses several additional but different variables are required. Email address for example is required for sending the data via email but it isn't for sending it via XML.

Assuming we had a database table which stored 'Data Delivery Choices' (XML, Email, or Post), would it be best to store the additionally required variables in this table, meaning if XML was selected the email field would be empty in that row, or would it be better to make three new tables to store the vairables associated with each possible choice in the 'Data Delivery Choices' table and then associate entries in these tables via the 'Data Delivery Choices' PK?

Or doesn't it matter which way it's done?

For the purposes of the question forget the fact me may already hold the users email address etc... elsewhere.

Thanks!

Answer 1

Some RDBMS (PostgreSQL for example), allow for table inheritance. So you can define DataDeliveryChoices and then create 3 additional tables that inherit from it DataDeliveryXML, DataDeliveryEmail, ...

MSSQL allows you to store (and query) an XML document in a column, so you can store the additional data in XML (not classic database design, but very flexible should you need to add some data fields, without changing the schema).

Your way of adding three additional tables is IMO also an acceptable solution.

The possibilities are endless :)