----- Original Message -----
From: "Bruno B B Magalháes" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
