Just searching for opinions around the following 2 situations.

There exists a table where we store our outgoing sms-messages. Each time our services transmits reasonably limited rate message, it stores them within this table... up to now, all of the information that should be saved has developed in the same format.

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 among the systems has implemented a totally new API that we have to integrate with this takes a bunch more parameters. 1 of those additional parameters may be the "Command". Based on which command we are delivering, you will find between 4 and 8 additional parameters we're needed to transmit. For simplicities sake, we'll say there's only two instructions... "InitialSend" &lifier "AnniversarySend"

Clearly it might quite the horrible DB design to simply add all these extra posts towards the finish in our existing table so... we reckon we have two options.

Option 1.

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

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

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

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

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 that one based on our DBA are purist. Each possible combination is strongly defined. The associations are obvious which is the very best artist.

From the POV, the cons are development time, quantity of touch points, complex retrieval rules/methods for messages with various command types, and insufficient reusability... a brand new command about this Mobile Network or any other network getting within this approach requires DB Level Design and Implementation... not only code level.

Option 2.

This method is to design one dynamic implementation with less, more multiple-use structures.

ParamterTypeID  int PK NOT NULL Identity
ParamterType    nvarchar(50)

This table will contain all known parameters for any messages

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

So benefits and drawbacks on that one.

Cons: You've less apparent visibility in regards to what params are related to what messages Gleam small performance problem... N card inserts per message rather than just 2

Pros: It is a hell of the lot simpler to build up against (imho). You just get a listing of Parameters Names -> Values back for any given messageID

It is also far more multiple-use... when the oddball network adds a brand new command, a brand new parameter on the command or even when another network arrives and implements an identical "I would like more informationInch API, we do not need any structural changes on our bodies.

SO... How would you react ?


Why must you store these details? Could it be reported on? Looked for? Employed for summaries and categorical analysis? In tangible-time? Frequently?

Do Less

If the information is basically for logging, place it inside a text or xml area and be done with it. YAGNI (You Ain't Gonna Require It) appears likely...


Not understanding exactly what the information is employed for, no-one can answer this, including you.

Yes, a completely-stabilized logical database structure is excellent and offers clearness etc. But could it be helpful?

Not every information is gold some is simply CYA