Structure

[john.]

One of the most annoying thing I find is that db.sql file. It sucks. It's hard to interpret, because it contains **** loads of data. Using a tool like MySQL Workbench or phpMyAdmin works pretty good, but requires you to dump and overwrite your sql files. Here is an alternative simple but better route:

Create a db/ folder. (Yep, your database structure deserves one directory itself!)

Make one file for each table. Put some effort and use DROP TABLE IF EXISTS; and such to easily help your drop and create tables when importing files.

Now you can easily import the tables you only need, if you change the users.sql table, you would import that table. Only want to alter the file and not drop it? Make a users_2.sql, users_altered.sql etc.

Sure, this works fine when you only have a small amount of tables, but what if you come at a point where you have 30-40 tables. It would suck importing these one by one. Well, you shouldn't. Merge the files! By applying the naming convention above, (User_alter) you would make sure that the files would be merged in a correct order. On Windows you would do:

 

copy *.sql db.sql

 

AND BAM! You have one kick-ass db.sql to distribute. So to get rid of the headache looking for the db.sql, you may want to put all your table files in a dev/ sub folder within db/.

This was a simple route you can take, it's definitely better than the db.sql way.

The best way to accomplish this is using a migration tool. A tool that does all of this for you, allows you to fancy rollbacks and seeding data. One example is using Phinx. http://phinx.org/

A PHP Database Migration you can use.

 

What do you think? Is the db.sql file enough for you? Does it make sense structuring your database files in a way like this? Have you used a migration tool? Would you try one? If not, why not?

[Dayo]

One of the most annoying thing I find is that db.sql file. It sucks. It's hard to interpret, because it contains **** loads of data. Using a tool like MySQL Workbench or phpMyAdmin works pretty good, but requires you to dump and overwrite your sql files. Here is an alternative simple but better route:

Create a db/ folder. (Yep, your database structure deserves one directory itself!)

Make one file for each table. Put some effort and use DROP TABLE IF EXISTS; and such to easily help your drop and create tables when importing files.

Now you can easily import the tables you only need, if you change the users.sql table, you would import that table. Only want to alter the file and not drop it? Make a users_2.sql, users_altered.sql etc.

Sure, this works fine when you only have a small amount of tables, but what if you come at a point where you have 30-40 tables. It would suck importing these one by one. Well, you shouldn't. Merge the files! By applying the naming convention above, (User_alter) you would make sure that the files would be merged in a correct order. On Windows you would do:

copy *.sql db.sql

AND BAM! You have one kick-ass db.sql to distribute. So to get rid of the headache looking for the db.sql, you may want to put all your table files in a dev/ sub folder within db/.

This was a simple route you can take, it's definitely better than the db.sql way.

The best way to accomplish this is using a migration tool. A tool that does all of this for you, allows you to fancy rollbacks and seeding data. One example is using Phinx. http://phinx.org/

A PHP Database Migration you can use.

What do you think? Is the db.sql file enough for you? Does it make sense structuring your database files in a way like this? Have you used a migration tool? Would you try one? If not, why not?

For GL i have started to make an XML file for the database layout this way I can write scripts to build the SQL, to upgrade versions, build documentation etc... I think the start of the work has been added to the repository hope to get it finished in the next week or so

[Dave]

I do much prefer systems which are entirely modular. Each module then has it's own installation/upgrade scripts which get run depending on detection of any previous versions.

Basically means when you install a new module into the site you upload the files and the installation script makes any database changes. Coupled with a half decent hooking system this makes module development a breeze. The system I'm talking about can be seen in Magento (http://magento.com)