Bruno B B Magalháes <[EMAIL PROTECTED]> wrote on 03/14/2006 12:41:35 PM:
> 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 > > But I am a little bit stuck with this: > > Should I use a column to mark if the moviment has been executed? > About income and outcome, should I use a column called moviment_type or > just put a negative value when is an outcome for exampe? > > Does anyone ever made something like that, any other idea that could > improve my little system? > > Best regards, > Bruno B B Magalhães > It all depends on what accounting style you want to use whether you want single-entry or double-entry bookkeeping. There are pros and cons to both styles. The best answer comes from you: If you were an accountant, how would you write this out on paper? Once you figure out what you paper books should look like you should be able to manage your electronic data almost identically to how you manage your paper data. If you are used to seeing a list of debits and credits where each line represents one account (end) within a single transaction (these lines always appear in pairs) then you want to use one type of table. If you want to see each transaction on a single line that also lists both ends of the transaction, you use a table similar to the one you made. If you have a flag for transaction type (debit,credit, etc) then your amounts should almost always be listed as positive values. It's the position of the account (assignor or drawee) and the transaction type that determines the sign (+/-) to apply to the value as you apply the value to an account. Should you have a flag for the status "movement complete" ? I would say yes but instead of a simple checkbox, you could store a date value. That gives you two pieces of information a) if the date is null then the movement is not complete. b) if the date is NOT null then the movement is complete and you know when it finished. As I said before, how you set up your accounting tables really depends on the style of accounting you want to do. Check with a bookkeeper or an accountant for more details. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
