My web app uses a number of photo hosting APIs. What would be an effective database design to store their limitations?

Question

For the sake of an example, let's say I'm using Picasa, Flickr and 500px APIs, and that each of the services has limitations on these simplified parameters:

• overall account space
• individual file size

I have a database table called external_services where I store the details about those services, my developer access tokens, etc., and I need to refer to the above limits in my application. I can think of the following ways to store that information:

1) Add columns to it to keep the information inline.

+--------+-------+--------+-
| Name   | Space | File   | …
+--------+-------+--------+-
| Flickr | 1 TB  | 200 MB | …
+--------+-------+--------+-
| Picasa | 15 GB | 75 MB  | …
+--------+-------+--------+-
| 500 px | 60 GB | ?? MB  | …
+--------+-------+--------+-

2) Alternatively, keep a separate meta table called external_services_details with a one-to-many relation.

+--------+------------+--------+
| Name   | Parameter  | Value  |
+--------+------------+--------+
| Flickr | acct_space | 1 TB   |
+--------+------------+--------+
| Flickr | max_file   | 200 MB |
+--------+------------+--------+
| Picasa | acct_space | 15 GB  |
+--------+------------+--------+
| Picasa | max_file   | 75 MB  |
+--------+------------+--------+
| 500 px | acct_space | 15 GB  |
+--------+------------+--------+
| …      | …          | …      |

My problem is I can't tell the difference between these two approaches in terms of both options being future-proof, I mean I can't foresee a scenario where there will be a difference. Will I regret picking one over another? The reason I ask this is that I'm sure better programmers than I am must have dealt with this before and learned their lessons, and that there already is an answer somewhere.

Now, on top of that, there are also specific limits of each of the services. For example, Flickr requires that Images can be no more than 31.25 times wider than they are tall, and in it's Fair Usage Policy, 500px limits new users to a maximum of 20 new photographs/images per week.

My thinking is this: if the requirements differ, it feels like a better way would be to store them in a meta table, with a flexible number of rows linked to each service. But if I start incorporating this logic into the app, I'll have to normalize the data I have anyway, that is, if Flickr limits the image ratio, I'd have to have an answer to that for each of the remaining services, even if they don't list that limitation - then I'd have to have ∞ stored there or something. Which kind of means that the structured table approach does actually make more sense here, as it enforces that structure.

What would be the effective way to handle this? Is there a name to this pattern?

I could have asked this on DBA, but a portion of this question deals with getting the right application logic, and that is software design.

Sorry about the poor table layout design

References. The values in the tables above are real, I researched. If someone somehow ends up on this page searching for the actual limits, here they are:

• Flickr,
• Picasa,
• 500px.

Answer 1

For any parameter for which your application has to interpret the semantics, your option 1 is IMHO the better one since it is simpler, more expressive and the additional flexibility of your option 2 does not bring you a benefit, since when you later introduce new, additional parameters you would need to change your application either. If some of the services do not have a specific limit, set the value to NULL, that is what NULL is for.

Your option 2 makes only sense when it is very costly to change the database structure later, much more costly to change the application, and you expect lots of new "limit parameters" you cannot foresee yet.

The more interesting question is, what to do with complex individual rules which cannot easily be encoded into a parameter column. You have basically the following options:

1. do not validate them at all, just display a descriptional text to the user for this limits. For this case, you just need an additional text column, holding the text. This might be ok, but can give you some hassle when you application needs to get localized.

2. Validate them with a non-generic validation code, specifically written for the service (you will typically utilize the strategy pattern for this). This case does not make much sense to be implemented in your database. This is ok when you only want to support a small size of photo APIs, and you can provide easily and quickly updates for your application when the rules are changed by the API provider. You might even consider to use this as the only approach, without the hassle of a database table.

3. Make it possible to store complex rules in the DB, maybe a "specific rule" text column, where you can provide a formula like height>0 AND width>0 AND width/height <1. This might need some more effort than the first two alternatives, since you have to write a formula interpreter, but is surely the most "future-proof" approach. You have to decide for yourself if this is worth the effort.