List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:March 14 2006 5:48pm
Subject:Re: Accountability with MySQL
View as plain text  
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

Thread
Accountability with MySQLBruno B B Magalháes14 Mar
  • Re: Accountability with MySQLMartijn Tonies14 Mar
    • Re: Accountability with MySQLBruno B B Magalháes14 Mar
  • Re: Accountability with MySQLSGreen14 Mar
    • Re: Accountability with MySQLMartijn Tonies14 Mar
    • Re: Accountability with MySQLRhino14 Mar
    • Re: Accountability with MySQLMartijn Tonies14 Mar
    • Re: Accountability with MySQLRhino15 Mar
      • Re: Accountability with MySQLJames Harvard15 Mar
    • Re: Accountability with MySQLMartijn Tonies15 Mar
    • Re: Accountability with MySQLMartijn Tonies15 Mar
    • Re: Accountability with MySQLRhino15 Mar
    • Re: Accountability with MySQLMartijn Tonies15 Mar
      • Re: Accountability with MySQLStephen Cook16 Mar
    • Re: Accountability with MySQLRhino15 Mar
      • Re: Accountability with MySQLJames Harvard16 Mar
    • Re: Accountability with MySQLMartijn Tonies16 Mar
      • Re: Accountability with MySQLMark Leith16 Mar
    • Re: Accountability with MySQLMartijn Tonies16 Mar
      • Re: Accountability with MySQLJames Harvard16 Mar
      • Re: Accountability with MySQLAlec.Cawley16 Mar
        • Re: Accountability with MySQLMartijn Tonies16 Mar
          • Re: Accountability with MySQLAlec.Cawley16 Mar
          • Re: Accountability with MySQLJames Harvard16 Mar
          • RE: Accountability with MySQLGordon16 Mar
            • On MySQL Views (Re: Accountability with MySQL)Martijn Tonies16 Mar
    • Re: Accountability with MySQLMartijn Tonies16 Mar
    • Coalesce (was: Re: Accountability with MySQL)Martijn Tonies16 Mar
    • True Propositions (was Re: Accountability with MySQL)Martijn Tonies16 Mar
  • Re: Accountability with MySQLMartijn Tonies14 Mar
    • Re: Accountability with MySQLSGreen14 Mar
      • Re: Accountability with MySQLMartijn Tonies14 Mar
  • Re: Accountability with MySQLRhino14 Mar
  • Re: Accountability with MySQLRhino14 Mar
Re: Accountability with MySQLBruno B B Magalháes14 Mar
  • Re: Accountability with MySQLRhino15 Mar
Re: Accountability with MySQLMartijn Tonies16 Mar
Re: Accountability with MySQLMartijn Tonies16 Mar