Author Topic: Database design  (Read 7231 times)

Stenner

  • Newbie
  • *
  • Posts: 8
    • View Profile
    • www.proremus.dk
Database design
« on: March 27, 2009, 08:44:19 am »
I've been looking through the database tables that OpenGoo is based on, and that has left me a bit puzzled. Apparently innoDB tables are used, but there's not a single relation/constraint in the whole database. Why the choise of innoDB then? Because of row vs table locking?  Besides that, innoDB takes up a whole lot more space according to http://dotnot.org/blog/archives/2005/10/12/mysql-myisam-vs-innodb/

What really worries me though, is that there's no consistency checks at the database level at the moment. This means that it's completely up to the php-developer to check up on any cascading effects. (that's a sad job)
I'm aware that it's quite a task to change anything at the database level, but it's certainly something to consider for a next version of the system.

Another benefit from having relations would be that it's easier to reverse engineer the database and make an E/R diagram of the whole thing. As it is right now, there's a lot of guessing involved.

Just my 2 €-cents

ignacio

  • Hero Member
  • *****
  • Posts: 1703
    • View Profile
Re: Database design
« Reply #1 on: March 27, 2009, 09:50:40 am »
The main reason for using InnoDB is database transactions. MyISAM doesn't support them, and programatically rolling back database sentences would be really too much work. Checking for constraints is a little bit easier.

The absence of constraints or relations at the database level is a limitation of the framework OpenGoo is based on. We will definitely evaluate this when designing version 2.0.

Also note that when installing you can choose to use MyISAM as the database engine, but there will be no transactions and so your OpenGoo could remain inconsistent. But the option is still there if your MySQL doesn't support InnoDB.

Your advice is very welcome. Thank you.