From: Martijn Tonies
Date: March 14 2006 5:48pm
Subject: Re: Accountability with MySQL
List-Archive: http://lists.mysql.com/mysql/195803
Message-Id: <063601c6478f$9105f520$cd02a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Hello Bruno,

> 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',

What a weird default date -> it's rather invalid, isn't it?

>   `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
>

btw, given all the "default" clauses, did you know you can actually
put a complete empty "moviment" into this table? Doesn't say a thing.
You might want to change that a bit :-)

> 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?

Calculating summaries etc is, I think, much easier if you just do a minus.

> Does anyone ever made something like that, any other idea that could
> improve my little system?

Well, it depends on what you're trying to do and how you want to
query data.

You could also decide to create two tables -> one for positives and one
for negatives.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com