List:General Discussion« Previous MessageNext Message »
From:Rhino Date:March 14 2006 6:31pm
Subject:Re: Accountability with MySQL
View as plain text  
----- Original Message ----- 
From: "Bruno B B Magalháes" <magalhaes@stripped>
To: <mysql@stripped>
Sent: Tuesday, March 14, 2006 12:41 PM
Subject: Accountability with MySQL


>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
>
One small observation: if your column names are meant to be in English - as 
suggested by your use of "amount", "drawee", etc. I think you should also 
use an English word where you are currently using "moviment". "Moviment" is 
not an English word. There _is_ an English word "movement" that might be 
what you want but I suspect that "transaction" would be the term most 
English-speakers would use in the table you describe. Your email address 
indicates that you are from Brazil so I'll assume you are a 
Portuguese-speaker, not a native English-speaker. This suggestion is only 
meant to be a friendly suggestion, not an insult to your English which is 
very very good. :-)

> But I am a little bit stuck with this:
>
> Should I use a column to mark if the moviment has been executed?

That depends. You could decide to only add the row to the table if it has 
been executed; then, you would know implicitly that every entry has been 
executed. On the other hand, if you want to show transactions that have not 
yet been executed, a column that indicates whether the transaction has been 
executed would probably be a good idea.

It might be even better to display details about the transaction that refer 
to its successful execution rather than just showing a yes/no flag. Perhaps 
you could store the timestamp that shows when the transaction was completed 
and maybe the identity of the person or program that completed the 
transaction or even a copy of the document generated by the transaction (or 
a link to this document). For instance, if a receipt was issued for this 
transaction, you might want to store an image of the receipt (or a link to 
the image) in the table.

> About income and outcome, should I use a column called moviment_type or
> just put a negative value when is an outcome for exampe?
>
Either approach should work. It would probably be easier to simply store the 
sign of the transaction with the amount.

> Does anyone ever made something like that, any other idea that could
> improve my little system?
>
I think the question you need to ask yourself is "What do I want to be able 
to find out about a transaction after it has taken place?". If you decide 
you want to know what currency was involved in the transaction, you need to 
record that in your table. If you decide you want to know the serial numbers 
of the banknotes used in the transaction, you need to record that. And so 
on. It might be wise to talk to someone in the bank and ask them what things 
they need to find out while a transaction is taking place and afterwards.

You may want to try asking your users (or their managers) what sorts of 
things people ask for but can't get from the old system; these are prime 
candidates for things that you could add to your new system.

I'm a little surprised that your amount column is an integer. Does the 
Brazilian currency not have a fractional component? Here in Canada, monetary 
amounts are always decimal numbers, like $123.45, meaning one hundred and 
twenty three dollars and forty five cents. Or is your currency like Japanese 
yen, which have no fractional part?

--
Rhino 



-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.2.2/280 - Release Date: 13/03/2006

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