I suggest a more general solution to this requirement: its called "Dimensions":
Dimensions
==============
A "dimension" is an additional information on a split booking.
One split booking can have multiple dimensions (three normally should be enough).
Every "dimension" is a tree structure with root, branches and leaves.
create table dimension (
dimguid char ( 32 ) primary key, // Global Unique Identifier
dimwhich smallint default 1, // Which dimension [1..3]
dimtype char ( 1 )
check ('R', 'N', 'L'), // Root, Node, Leave
dimname char ( 30 ), // Any name. Should be unique within same dim
dimno long, // Any number. Should be unique within same dim
dimparent char ( 32 ) // Reference to parent entry
references dimension.dimguid
);
A split booking needs three additional columns for dimensions 1, 2 and 3. Column types are
references to the global unique ID of a dimension entry (dimension.dimguid).
The root entry gets the name of that dimension (e.g. "costcenter").
This root names should be used for column headers when displaying or printing a split booking.
Screens:
"Dimension-Management" similar to Account-Management with three root entries for the
three dimensions. Default names could be "dim 1" to "dim 3".
"Journal" to be extended with three columns for the dimensions. Each one should be usable as
a filter to only display all transactions and/or splits containing references to a specific
dimension or even dimension entry. E.g.: only display/print all transactions which have at least
on split booking with a reference to dimension1. Something like:
"select ... where split.dim1 is not null..."
Also this display/print should be filterable by a time period.
Additional functionality could be to restrict dimensions only to be used on split bookings
which have an account of type income or expense. Anyway this restriction has to be configurable
because it does not make sense for all kinds of uses.
I suggest a more general solution to this requirement: its called "Dimensions":
Dimensions
==============
A "dimension" is an additional information on a split booking.
One split booking can have multiple dimensions (three normally should be enough).
Every "dimension" is a tree structure with root, branches and leaves.
create table dimension (
dimguid char ( 32 ) primary key, // Global Unique Identifier
dimwhich smallint default 1, // Which dimension [1..3]
dimtype char ( 1 )
check ('R', 'N', 'L'), // Root, Node, Leave
dimname char ( 30 ), // Any name. Should be unique within same dim
dimno long, // Any number. Should be unique within same dim
dimparent char ( 32 ) // Reference to parent entry
references dimension.dimguid
);
A split booking needs three additional columns for dimensions 1, 2 and 3. Column types are
references to the global unique ID of a dimension entry (dimension.dimguid).
The root entry gets the name of that dimension (e.g. "costcenter").
This root names should be used for column headers when displaying or printing a split booking.
Screens:
"Dimension-Management" similar to Account-Management with three root entries for the
three dimensions. Default names could be "dim 1" to "dim 3".
"Journal" to be extended with three columns for the dimensions. Each one should be usable as
a filter to only display all transactions and/or splits containing references to a specific
dimension or even dimension entry. E.g.: only display/print all transactions which have at least
on split booking with a reference to dimension1. Something like:
"select ... where split.dim1 is not null..."
Also this display/print should be filterable by a time period.
Additional functionality could be to restrict dimensions only to be used on split bookings
which have an account of type income or expense. Anyway this restriction has to be configurable
because it does not make sense for all kinds of uses.
Questions? mailto:hlemcke@web.de