>>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 '-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 '-00-00',
`transaction_executed_date` date NOT NULL default '-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 '-00-00',
`transaction_executed_date` date NOT NULL default '-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 trans
