Bruno B B Magalháes <magalhaes@stripped> wrote on 03/14/2006 12:41:35
PM:
> I am designing a simple accountability system so all the partners can
have
> direct access to it by intranet.
>
> I was designing the data model, and came up with this:
>
> CREATE TABLE `moviments` (
> `moviment_id` int(20) NOT NULL auto_increment,
> `moviment_date` date NOT NULL default '0000-00-00',
> `moviment_description` char(200) NOT NULL default '',
> `moviment_assignor` char(80) NOT NULL default '',
> `moviment_drawee` char(80) NOT NULL default '',
> `moviment_amount` int(20) NOT NULL default '0',
> PRIMARY KEY (`moviment_id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
>
> But I am a little bit stuck with this:
>
> Should I use a column to mark if the moviment has been executed?
> About income and outcome, should I use a column called moviment_type or
> just put a negative value when is an outcome for exampe?
>
> Does anyone ever made something like that, any other idea that could
> improve my little system?
>
> Best regards,
> Bruno B B Magalhães
>
It all depends on what accounting style you want to use whether you want
single-entry or double-entry bookkeeping. There are pros and cons to both
styles. The best answer comes from you: If you were an accountant, how
would you write this out on paper? Once you figure out what you paper
books should look like you should be able to manage your electronic data
almost identically to how you manage your paper data.
If you are used to seeing a list of debits and credits where each line
represents one account (end) within a single transaction (these lines
always appear in pairs) then you want to use one type of table. If you
want to see each transaction on a single line that also lists both ends of
the transaction, you use a table similar to the one you made.
If you have a flag for transaction type (debit,credit, etc) then your
amounts should almost always be listed as positive values. It's the
position of the account (assignor or drawee) and the transaction type that
determines the sign (+/-) to apply to the value as you apply the value to
an account.
Should you have a flag for the status "movement complete" ? I would say
yes but instead of a simple checkbox, you could store a date value. That
gives you two pieces of information
a) if the date is null then the movement is not complete.
b) if the date is NOT null then the movement is complete and you know when
it finished.
As I said before, how you set up your accounting tables really depends on
the style of accounting you want to do. Check with a bookkeeper or an
accountant for more details.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
