Creating Tables for your Experiment

[This assumes you've already set up yourself with a relational DBMS (MySQL and PostgreSQL are usually winners) and have access to create tables in your database. FYI: I'm using MySQL.]

Setting up a DBMS requires quite a bit of thought and foresight. The flexibility available is very broad, so the real constraints are more with the user rather than the infrastructure. Some questions to consider:

At what stage of processing do I want to put the data into tables (e.g. raw time series, motion-corrected time series, “cleaned” time series, just the HRFs for conditions)?

What information do I want to index (e.g. ROI identifiers, voxels, subject identifiers)?

What meta data do I want to hold?

Do I want primary key and foreign key usage?

What sort of relationships do I want between tables?

For first steps, it might be best to start simple and let the complexities emerge and be handled as more experience with the infrastructural tools is gained.

For actually creating the tables in my database, I’ve found MySQL Administrator (part of the MySQL GUI Tools) to be extremely straightforward and helpful. Of course, procedures for creating tables can also be scripted and make use of simple SQL statements such as:

create table afni_db_test (x INT, INDEX(x), y INT, INDEX(y), z INT, INDEX(z), consistent float, filler float, inconsistent float);

In this line, the table created is called afni_db_test, columns are x, y, z, filler, and inconsistent, with x, y, and z being indices.
And here is that statment embedded in a python script (using the MySQLdb module):

#!/usr/bin/python

import os
import sys
import commands
import MySQLdb

_user='you'
_host='yourhost'
_passwd='yourpassword'
_db='test'

try:
    connection = MySQLdb.connect(host=_host,user=_user,db=_db,passwd=_passwd)
except MySQLdb.Error, e:
    print "Error %d: %s" % (e.args[0], e.args[1])
    sys.exit (1)

cursor = connection.cursor()

create_table_statement = 'create table afni_db_test (x INT, INDEX(x), y INT, INDEX(y), z INT, INDEX(z), consistent float, filler float, inconsistent float);'

cursor.execute(create_table_statement)

Choosing the type of storage engine you would like to use is also something to consider and dependent on what you’d like to do with your data. By default, MySQL uses MyISAM, but InnoDB is also popular. The InnoDB engine supports primary and foreign key constraints, while the MyISAM engine does not. Changing the type of storage engine is very easy in the MySQL Administrator GUI. Just go to the ‘Catalogs’ tab, select your Schemata and you get a list of the tables in your database. Then, double-click the table you want and its schema will open in a new window. There, just choose the ‘Table Options’ tab and in the drop-down menu you can select a new storage engine (click for bigger image):

More about storage engines available with MySQL can be found here.