Which approach would you use for this specific DB Design Issue? [closed]

Question

Just looking for opinions on the following 2 scenarios.

We have a table where we store our outbound sms-messages. Everytime one of our services sends a premium rate message, it stores them in this table... to date, all the important information that needs to be stored has been in the same format.

SMSMessages
----------------------
ID              int PK NOT NULL Identity
Mobile          nvarchar(50) -- the number we're sending to
NetworkID       int FK -> Table containing networks (voda, o2, etc...)
ShortcodeID     int FK -> Table containing our outbound shortcodes
DateSent        DateTime

Now one of the networks has implemented a completely new API that we need to integrate with that requires a bunch more parameters. 1 of these additional parameters is the "Command". Depending on which command we're sending, there are between 4 and 8 additional parameters we are required to send. For simplicities sake, we'll say there's only two commands... "InitialSend" & "AnniversarySend"

Obviously it would quite the horrible DB design to just add all these additional columns to the end of our existing table so... we reckon we've two options.

Option 1.

Create many new tables, specific to each command, linked back to the original table.

SMSMessages_CommandTypes --Contains "InitialSend" & "AnniversarySend" + other commands
--------------------------
CommandTypeID   int PK
Command     nvarchar(50)


SMSMessages_OddBallNetwork
--------------------------
ID              int PK, FK --> SMSMessages.ID
CommandTypeID   int FK ---> SMSMessages_CommandTypes


SMSMessages_OddBallNetwork_InitialSend
--------------------------------------
ID              int PK, FK --> SMSMessages.ID
Param1          nvarchar(50)
Param6          nvarchar(50)
Param9          nvarchar(50)
Param14          nvarchar(50)

SMSMessages_OddBallNetwork_AnniversarySend
--------------------------------------
ID              int PK, FK --> SMSMessages.ID
Param1          nvarchar(50)
Param2          nvarchar(50)
Param7          nvarchar(50)
Param9          nvarchar(50)
Param12          nvarchar(50)

//There are 4 other Command Types as well so 4 More Tables...

The pro's to this one according to our DBA are all purist. Each possible combination is strongly defined. The relationships are clear and it is the best performer.

From my POV, the cons are development time, number of touch points, complex retrieval rules/procedures for messages with different command types, and lack of reusability... a new command on this Mobile Network or another network bringing in this approach requires DB Level Design and Implementation... not just code level.

Option 2.

This option is to try and design one dynamic implementation with fewer, more reusable structures.

SMSMessages_AdditionalParameterTypes
------------------------------------
ParamterTypeID  int PK NOT NULL Identity
ParamterType    nvarchar(50)

/*
This table will contain all known parameters for any messages
CommandName
Param1
Param2
etc..
*/

SMSMessages_AdditionalParameters
--------------------------------
ID              int PK NOT NULL Identity
MessageID       int FK --> SMS Messages
ParamTypeID     int FK --> SMSMessages_AdditionalParameterTypes
Value           nvarchar(255)

So pros and cons on this one.

Cons: You've less obvious visibility as to what params are linked with what messages There's also a small performance issue... N inserts per message instead of just 2

Pros: It's a hell of a lot easier to develop against (imho). You simply get a list of Parameters Names -> Values back for a given messageID

It's also alot more reusable... if the oddball network adds a new command, a new parameter on a command or even if another network comes along and implements a similar "I want more info" API, we don't need any structural changes on our system.

SO... What would you do ?

Answer 1

Why

Why do you need to store this information? Is it reported on? Searched for? Used for summaries and categorical analysis? In real-time? Frequently?

Do Less

If this data is merely for logging, stick it in a text or xml field and forget about it. YAGNI (You Ain't Gonna Need It) seems likely...

Seriously

Without knowing what the data is used for, no one can answer this question, including you.

Yes, a fully-normalized logical database structure is great and provides clarity etc. But is it useful?

Not all data is gold; some is just CYA

Answer 2

Option 2 wins for me. For the performance issue, you should only have extra inserts for the oddball network, and that one is going to be a problem anyway. For the visibility issue, I think that is a matter of perception. After working with the new system for a while, it will probably become second-nature to see the message parameter requirements.