List:General Discussion« Previous MessageNext Message »
From:Rhino Date:March 15 2006 12:30am
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 2:07 PM
Subject: Re: 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. :-)
>
> Ups, that´s right, I misspelt movement, you know beeing a partner and also
> being the financial director, technology director and CEO some times can
> be exhaustive. Well, at least we are growing.. (the opposite of my
> weekends, that seams to be getting smaller and smaller).  But thanks, I
> also agree transaction is more suitable.
>>> 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.
>
> It´s a good idea, but for now, overkill... I mean here is my new design:
>
> CREATE TABLE `earnings` (
>  `transaction_id` int(20) NOT NULL auto_increment,
>  `transaction_planned_date` date NOT NULL default '0000-00-00',
>  `transaction_executed_date` date NOT NULL default '0000-00-00',
>  `transaction_description` char(200) NOT NULL default '',
>  `transaction_assignor` char(80) NOT NULL default '',
>  `transaction_amount` int(20) NOT NULL default '0',
>  PRIMARY KEY  (`transaction_id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
>
> CREATE TABLE `earnings` (
>  `transaction_id` int(20) NOT NULL auto_increment,
>  `transaction_planned_date` date NOT NULL default '0000-00-00',
>  `transaction_executed_date` date NOT NULL default '0000-00-00',
>  `transaction_description` char(200) NOT NULL default '',
>  `transaction_assignor` char(80) NOT NULL default '',
>  `transaction_amount` int(20) NOT NULL default '0',
>  PRIMARY KEY  (`transaction_id`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
>
> I think it is a good model for start...
>
>>> About income and outcome, should I use a column called moviment_type or
> just put a negative value when is an outcome for example?
>>>
>> 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.
>
> What I want? Well that my partners stop asking me to send the spreadsheet
> every week... :) They want to take a look the company´s profitability, if
> all payments have been made, if all clients have payed... As I am the one
> who make every transaction I simple would input it into DB (direct) and
> they would access a simple page with a transactions list and graphics of
> the current month as default, so they could choose what period they want.
>
Okay, that's fine but it's a little more general than I had in mind. I was 
trying to suggest that you think of specific problems that you need to be 
able to solve with the table :-)

>> 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?
>
> Yes we have decimal, but, we use the dot for thousand, and the comma for
> decimal values... Is it possible to make MySQL accept this? :)
>
Normally, when you define a number as a decimal number in a database, no 
actual decimal symbol is stored in that column. The decimal symbol, whether 
it is a dot or a comma, is usually 'virtual': it is not stored. When you 
display the number later in a query, there is usually some way that you can 
specify the decimal symbol you want and you can often choose what separator 
you want to represent 1000. The exact method for specifying your decimal 
symbol and thousands separator is usually different for each database and 
programming language. (You can often use SQL to format the number the way 
you like but you could give this job to the programming language.)

I haven't played with decimal symbols in MySQL - I mostly use DB2 - but I 
just took a quick look at the manual to see how you could control the 
decimal symbol and thousands separator in MySQL. Unfortunately, I didn't 
find it but maybe if you do a more thorough search you will find the right 
answer. There may be a built-in function that controls the formatting. Or 
maybe you'll have to write your own function for this purpose. Or maybe 
choosing the right character set will handle this for you automatically. I'm 
really not sure. If you want to do this formatting via Java, I can tell you 
how to accomplish it but if you are using Perl or php, or other languages, I 
don't know.


--
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