How would you design a solution to this problem?
unsure what would be the best approach -
I have a SQL table that stores SMS 3rd party providers.
Each provider support different protocols for its integration - (HTTP/SMPP).
According to the provider supported protocol it requires different parameters to be stored.
the HTTP providers stores -
1. URL endpoint
2. Api Key
The SMPP providers stores -
1. IpAddress
2. Port
3. Username
4. Password
Although they are different, they serve the same purpose therefore needs to exist in the same table.
So my question is, how would you handle this scenario both in the db and in the code?
Option 1 -
In the DB have a single table for
Provider with such schema -
Protocol is an enum of HTTP/SMPP
And the Custom fields are nvarchar fields that are used according the the Protocol value.
I can select all the providers into a generic Provider class.
Implement an IProvider interface and concrete types - ProviderSMPP and ProviderHTTP
Have a factory method that receives a list of Provider and returns a list of IProvider where I initialize each concrete type using a switch-case on the Protocol field mapping the custom fields into its concrete values.
Option2 -
Break the Provider table.
Leaving the Provider table only with the shared fields, and create a relationship tables for ProviderSMPPCredentials and ProviderHTTPCredentials
And join both tables?
What would you do?10 Replies
What do you mean?
I've seen people store stuff like that as JSON in databases. If not, what's wrong with including all the fields (you only have the two protocols, right?) and NULLing the stuff that doesn't apply?
hmmm, guess there's nothing wrong with it?
So I can dump aside all the abstraction and simply have a single
Provider class with all the columns in it.
The table isn't big and won't scale so having some null fields doesn't sounds too bad… I might have over complicated it
Ty!
Trying to improve by making sure I follow best practices so wondering what other would think as well.Was on mobile and apparently the entire message didn't load or something??? Anyway sorry
I guess you could even make that a constraint
What do you mean?
I don't know if it makes sense, but you could instruct most database engines to enforce that each "provider" has one of those kinds of information. Probably not necessary.
@thinker227 I see that too... no original message for context
Edit -
Another complication arise, Providers that integrates via SMPP might have multiple sets of credentials -
So for example it can have 3 sets of -
1. IpAddress
2. Port
3. Username
4. Password
So I must break the table apart into -
Provider and ProviderCredentials with a one-to-many relation.
ProviderCredentials gonna look like -
So basically http providers will have a single relationship row, with values in - Host/Token1/Token2, everything else will be null.
SMPP providers might have multiple relationship rows, with values in - Host/Port/Username/Password, Everything else will be null.
When selecting them I'll join the provider credentials and in the code merge every provider occurence and create a list of ProviderCredentials for it.
Still is the best approach?