Money database schema?




Dick Watson

It depends on what you have in mind and what you are expecting that I've
done vs. what I haven't done.

What I've done was sort of a proof of concept effort just to understand the
magnitude of the task and the nature of the database. There are many things
I haven't scratched the surface of, even in the DB, but especially in the UI
and business logic.

The largest single constraint to sharing it is that I don't have a
prototypical dataset. When I populate the thing to assess whether the schema
works, I use my real live data. And there are (a few) pieces of the code for
populating and normalizing it from the .CSV exported Money report data that
assumes some things like the names of the accounts that are non-USD
denominated. (This was just rapid prototyping for the proof-of-concept; more
code and the constraint goes away.) I have, however, used this code and the
queries and tables and linked tables/reports to import the entire account
transaction dataset (including investments, loans, and the cash (credit
card/checking/liability/etc.) accounts) and re-normalize it using as the
data source carefully crafted .CSV from several customized Money reports.

As noted elsewhere, this was not an attempt to crack into Money internals
through programmatic interfaces. There is LOTS of data I have no way to get
at using the technique I'm presently using. (Think settings, options, and
properties for payees, categories, accounts, etc. as a starting place of
what I can't get.) Since I can't get at this, there are lots of fields that
I haven't created--like details for an account--but the basic table
structure makes adding this kind of stuff pretty straightforward.

I also haven't done Scheduled Bills yet. (One reason is that there is lots
of data about scheduled bills that is write only in Money.) (Ironically, I
started this while thinking about the bill scheduling model and how to do a
query like forecast cash flow using SQL. That's still an open item.) I also
have never come up with a good SQL way to do running balance. I have some
thoughts about alternate methods, but that's probably moving the code up an
order of magnitude--and into C++ or similar--and I haven't gone there.

There are three prime areas I'd like to re-design based on the experience:

1) Accounts and what I called Vectors (Categories/Classification 1-n/etc)
should be derived from the same object/construct/table. Right now I have
them separate. GnuCash has some cool behaviors in this regard, but I've
never studied how they do it exactly. Basically the goal would be back to
double accounting 101: every transaction has two accounts. One or both may
be real world "accounts" or one may be a real world "category".

2) I'd like any account to be able to hold transactions denominated in
any/multiple tokens of exchange (dollars/shares/ounces of gold/hectares,
whatever) simultaneously and support multiple balances for a given account.

The db is in Access2003. All of the code for it is associated with
importing/normalizing the data and is in VBA.

That all having been said, let's take any further discussions offline. You
can figure out my email address from the one in the header.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question