database - Data modeling question

Question

My clients use one of the following when they sign up for my application:

1. Foo API (requires a "auth_key", "password", "email")
2. Acme API (requires a "secure_code", "username", "password")
3. Bar API (requires a "xyz_code", "pass_key")

(fake names, and about 15 more omitted for simplicity)

I would prefer not to have 10-15 tables in my database just for the different API integration options I offer (particularly when they're all for the same thing and they just choose 1 from the whole list).

My solution was this:

Make a api_configuration table with a column called api_name which holds a code for a specific API (e.g. "foo_api")

Make a table called credentials_attribute with a foreign key back to api_configuration, a column called name, and a column called value.

Then I build a UI for choosing an API. If they choose Acme API, it'll ask for a "secure_code", "username", and "password", and create a row in credentials_attribute for each of the name/value pairs.

On my ORM model for api_configuration I can make a method for looking up credentials_attribute values based on the current api_name.

Does this solution feel right, or is there another way you would do it, if you had to model a solution for this problem? Please explain your rationale as well (ie, better for performance, etc)

Answer 1

I would probably prefer to do this with a single table itself

Have a single UserAuthentication table with columns like IdentificationKey, AuthenticationCriteria1, AuthenticationCriteria2 and so on...

Number of AuthenticationCriteriaX columns = maximum number of criteria that any API will have. I am assuming it will be something reasonable like maybe 5 at the most but anything upto 15-20 is actually still is a pretty small table.

UserAuthentication table also has a api_key column which is a foreign key from an MASTER_API table which is the list of all supported API's

As for the UI part of the problem, i.e what label to show the user for any field from the UserAuthentication table, i think that is just a UI concern and as such you should just have the mapping specific to each api somewhere in your UI layer. The api_key column can be used for the translation as needed. The DB does not necessarily need to know those details, IMO.