We have what i think is a fairly run-of-the mill ledger table, with
columns id, account_id and amount.

In addition, we need a way to group/categorize/tag transactions on cost
bearers. Think this is often referred to as 'dimensions'..?


We're redesigning an older system with two dimensions, 'project' and
'department'. There's a table for each, containing mainly a name, and
two foreign key fields in the ledger table.
Also, the chart of accounts has a toggle for each dimension.

Thoughts on how to improve on the above?


For now we've just copied the old design, except for switching the
enable_departments/enable_projects toggles into many-to-many relations
between the accounts table and projects/depts. In hindsight I'm not
sure what we gain from that.


It all seems a bit awkward, and i'm sure there's a better way to go
about this. Someone willing to share their insight on the matter?

Isak

Isak Hansen wrote:
Rather than copy the old design, have you started from scratch and done
an ERD? (Entity Relationship Diagram)

general questions you should be asking include:
what are the entities we are trying to model?
what questions (queries/reports) do we expect toanswer when we are
done?
how do the relationships change among the entities?

A good data modeller is a useful person to have in this part of the
project. (this is someone who helps in the LOGICAL design, not a DBA
who usually deals with the Physical design of the Database.)

If your requirements are not too unusual, why not get a COTS accounting
package?

HTH,
Ed

(Commercial Off The Shelf)

accomplish? If so, you're on the right path. The reporting
requirements drive the structure of a dimensional model, not
the dependencies of the data.

dataman wrote:
Thanks for your replies.

Was thinking that a full unlimited-dimension design would be more
elegant, and common enough to get some hints here, but as the current
model meets our requirements we'll just stick with that.

We are starting over from scratch, as the old model was flawed in so
many ways. We've probably made a few bummers, but are confident it's
significantly better.


Have some, not 100% up to date, basic docs up at
<http://trac.lodo.no/lodo2/wiki/DatabaseModel>.

The project is ment to be open sourced in some way or part, but we're
not settled on the specifics yet, so drop us a note if you'd like to
use or contribute to the system.

And yes Ed, we probably shouldn't be working on this project, for a
variety of reasons..


Isak