List:General Discussion« Previous MessageNext Message »
From:SGreen Date:March 14 2006 6:15pm
Subject:Re: Accountability with MySQL
View as plain text  
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



Thread
Accountability with MySQLBruno B B Magalháes14 Mar
  • Re: Accountability with MySQLMartijn Tonies14 Mar
    • Re: Accountability with MySQLBruno B B Magalháes14 Mar
  • Re: Accountability with MySQLSGreen14 Mar
    • Re: Accountability with MySQLMartijn Tonies14 Mar
    • Re: Accountability with MySQLRhino14 Mar
    • Re: Accountability with MySQLMartijn Tonies14 Mar
    • Re: Accountability with MySQLRhino15 Mar
      • Re: Accountability with MySQLJames Harvard15 Mar
    • Re: Accountability with MySQLMartijn Tonies15 Mar
    • Re: Accountability with MySQLMartijn Tonies15 Mar
    • Re: Accountability with MySQLRhino15 Mar
    • Re: Accountability with MySQLMartijn Tonies15 Mar
      • Re: Accountability with MySQLStephen Cook16 Mar
    • Re: Accountability with MySQLRhino15 Mar
      • Re: Accountability with MySQLJames Harvard16 Mar
    • Re: Accountability with MySQLMartijn Tonies16 Mar
      • Re: Accountability with MySQLMark Leith16 Mar
    • Re: Accountability with MySQLMartijn Tonies16 Mar
      • Re: Accountability with MySQLJames Harvard16 Mar
      • Re: Accountability with MySQLAlec.Cawley16 Mar
        • Re: Accountability with MySQLMartijn Tonies16 Mar
          • Re: Accountability with MySQLAlec.Cawley16 Mar
          • Re: Accountability with MySQLJames Harvard16 Mar
          • RE: Accountability with MySQLGordon16 Mar
            • On MySQL Views (Re: Accountability with MySQL)Martijn Tonies16 Mar
    • Re: Accountability with MySQLMartijn Tonies16 Mar
    • Coalesce (was: Re: Accountability with MySQL)Martijn Tonies16 Mar
    • True Propositions (was Re: Accountability with MySQL)Martijn Tonies16 Mar
  • Re: Accountability with MySQLMartijn Tonies14 Mar
    • Re: Accountability with MySQLSGreen14 Mar
      • Re: Accountability with MySQLMartijn Tonies14 Mar
  • Re: Accountability with MySQLRhino14 Mar
  • Re: Accountability with MySQLRhino14 Mar
Re: Accountability with MySQLBruno B B Magalháes14 Mar
  • Re: Accountability with MySQLRhino15 Mar
Re: Accountability with MySQLMartijn Tonies16 Mar
Re: Accountability with MySQLMartijn Tonies16 Mar