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

If I understand the case correctly, it looks like yet another case of the gen-spec design pattern. Look up "generalization specialization relational modeling".

Tutorials on object modeling usually cover gen-spec, but tutorials on relational modeling often do not. But it's well understood, and there are some excellent articles on the web.

Answer 2

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.

Answer 3

If I understand you correctly, all these properties in the different APIs are conceptually tuples of the same 3 thing, under different names, right?

Your description is from the DB point of view - I will describe what I would do on the domain side (which may be directly mappable to your schema).

I would create a single class, e.g. UserLogin, with the 3 properties mentioned above (e.g. authenticationCode, userId, password), and a mapping of API names/codes to GUI property names. When the user selects the preferred API, I can display fields with the appropriate names on the GUI, and fill the values to the corresponding properties of UserLogin. If needed, UserLogin can also store the preferred login API for that user. This way UserLogin is mapped to a single table on the DB side. I may use another table to configure the property names for each known API.