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.
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? :)
