>>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? :) Thanks for everything, Bruno B B Magalhães -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
