Data structure of memoization in db
Question: What is the best data structure to cache (save/store/memorize) so many
function result in database. Suppose function calc_regress with flowing
definition in python:

    
    
    def calc_regress(ind_key, dep_key, count=30):
        independent_list = sql_select_recent_values(count, ind_key)
        dependant_list = sql_select_recent_values(count, dep_key)
        import scipy.stats as st
        return st.linregress(independent_list, dependant_list)
    

I see answers to [What kind of table structure should be used to store
memoized function parameters and results in a relational
database?](http://stackoverflow.com/questions/11147972/what-kind-of-table-
structure-should-be-used-to-store-memoized-function-parameter) but it seem to
resolve problem of just one function while I have about 500 function.


Answer: **Option A**

You could use the structure in the linked answer, un-normalized with the
number of columns = max number of arguments among the 500 functions. Also need
to add a column for the function name.

Then you could do a `SELECT * FROM expensive_func_results WHERE func_name =
'calc_regress' AND arg1 = ind_key AND arg2 = dep_key and arg3 = count`, etc.

Ofcourse, that's not a very good design to use. For the same function called
with fewer parameters, columns with null values/non-matches need to be
ignored; otherwise you'll get multiple result rows.

**Option B**

Create the table/structure as `func_name`, `arguments`, `result` where
'arguments' is always a kwargs dictionary or positional args but not mixed per
entry. Even with the kwargs dict stored as a string, order of keys->values in
it is not predictable/consistent even if it's the same args. So you'll need to
order it before converting to a string and storing it. When you want to query,
you'll use `SELECT * FROM expensive_func_results WHERE func_name =
'calc_regress' AND arguments = 'str(kwargs_dict)'`, where `str(kwargs_dict)`
is something you'll set programmatically. It could also be set to the result
of
[`inspect.getargspec`](https://docs.python.org/2.7/library/inspect.html#inspect.getargspec),
(or
[`inspect.getcallargs`](https://docs.python.org/2.7/library/inspect.html#inspect.getcallargs))
though you'll have to check for consistency.

You won't be able to do queries on the argument combos unless you provide all
the arguments to the query or partial match with `LIKE`.

**Option C**

Normalised all the way: One table `func_calls` as `func_name`,
`args_combo_id`, `arg_name_idx`, `arg_value`. Each row of the table will store
one arg for one combo of that function's calling args. Another table
`func_results` as `func_name`, `args_combo_id`, `result`. You could also
normalise further for `func_name` to be mapped to a `func_id`.

In this one, the order of keyword args doesn't matter since you'll be doing an
Inner join to select each parameter. This query will have to be built
programmatically or done via a stored procedure, since the number of joins
required to fetch all the parameters is determined by the number of
parameters. Your function above has 3 params but you may have another with 10.
`arg_name_idx` is 'argument name or index' so it also works for mixed kwargs +
args. Some duplication may occur in cases like `calc_regress(ind_key=1,
dep_key=2, count=30)` and `calc_regress(1, 2, 30)` (as well as
`calc_regress(1, 2)` with a default value for count <\-- this cases should be
avoided, the table entry should have all args); since the `args_combo_id` will
be different for both but result will obviously be the same. Again, the
inspect module may help in this area.

* * *

_[Edit]_ PS: Additionally, for the `func_name`, you may need to use a fully
qualified name to avoid conflicts across modules in your package. And
decorators may interfere with that as well; without a `deco.__name__ =
func.__name__`, etc.

PPS: If objects are being passed to functions being memoized in the db, make
sure that their `__str__` is something useful & repeatable/consistent to store
as arg values.

This particular case doesn't require you to re-create objects from the arg
values in the db, otherwise, you'd need to make `__str__` or `__repr__` like
the way [`__repr__` was intended to
be](https://docs.python.org/2/reference/datamodel.html#object.__repr__) (but
isn't generally done):

> this should look like a valid Python expression that could be used to
> recreate an object with the same value (given an appropriate environment).

