Android SQLite - New table VS. new DB

Question

I have a pretty complext DB on my app.

When the time came to add more functionality, I started wondering whether I should implement it by adding more tables to the existing database, or open an entirely new database for the added functionality and add the tables there.

Are there any ground rules regarding when to open a new DB? Are there any benchmarks available on the subject?

Answer 1

When the time came to add more functionality, I started wondering whether I should implement it by adding more tables to the existing database, or open an entirely new database for the added functionality and add the tables there.

Are there any ground rules regarding when to open a new DB? Are there any benchmarks available on the subject?

So this is very complex question and depends on more factors as character of application, required performance, character of tables, relations between them etc.

A lot of people, lot of opinions so everybody can tell you something different.

But my opinion is that you should have only one database(also when will have pretty more tables e.q. 10-20). Then also try to think about an usage of Singleton.

Always is better have all tables in one database especially in the case if those tables have something in common(you sometimes need to join two or more tables and if you put table(s) to another database this stuff will become impossible). Also you don't know(exactly now) if you'll update table(s), change relations between them etc. in a future(because of application update for example). On the other hand also structure of application is more clean and readable as if you had for example five *.db files on internal or external storage(i can say this depends on size of each because it's sick to place 100 MB .db file to internal storage).

And on question about implementation: I recommend you(as i mentioned above) to create only one DatabaseHelper that will wrap all required db logic. Then create classes(one class for one table) called <?>Tools for example UsersTools which will wrap CRUD operations and specific methods for specific table. I'm using this approach and never had problems.

Example of implementation:

Here is example of DatabaseHelper:

package com.sajmon.examples.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DataSource extends SQLiteOpenHelper {

    private static DataSource instance = null;

    public static final String DB_NAME = "<dbname>";
    public static final int DB_STARTING_VERSION = 1;

    public DataSource(Context cntx) {
        super(cntx, DB_NAME, null, DB_STARTING_VERSION);
    }

    /**
     * 
     * @param mContext as Context of Activity
     * @return <li>new instance of }DataSource object.</li>
     */
    public static DataSource getInstance(Context mContext) {
        if (instance == null) {
            instance = new DataSource(mContext);
        }
        return instance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String query = "create table " + SQLConstants.TEST_TABLE_NAME + " ("
                + SQLConstants.KEY_ID + " integer not null, "
                + SQLConstants.KEY_TYPE + " text null, "
                + SQLConstants.KEY_DATE + " text null" + ")";
        db.execSQL(query);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        String query = "drop table if exists " + SQLConstants.TEST_TABLE_NAME;
        db.execSQL(query);
        onCreate(db);
    }
}

SQLConstants:

package com.sajmon.examples.db;

public class SQLConstants {

    public static final String TEST_TABLE_NAME = "Test";
    public static final String KEY_ID = "id";
    public static final String KEY_TYPE = "type";
    public static final String KEY_DATE = "date";

}

TestTools example:

package com.sajmon.examples.db;

import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

public class TestTools {

private Context mContext;
private SQLiteDatabase db;

public TestTools(Context mContext) {
    this.mContext = mContext;
}

public boolean insert(Test t) throws SQLException {
    try {
        db = openWrite(DataSource.getInstance(mContext));
        ContentValues values = new ContentValues();
        if (db != null) {
            db.beginTransaction();
            values.put(SQLConstants.KEY_ID, t.getId());
            values.put(SQLConstants.KEY_TYPE, t.getType());
            values.put(SQLConstants.KEY_DATE, t.getDate());
            db.insertOrThrow(SQLConstants.TEST_TABLE_NAME, SQLConstants.KEY_TYPE, values);
            values.clear();
            db.setTransactionSuccessful();
            return true;
        }
        return false;
    }
    finally {
        if (db != null) {
            db.endTransaction();
        }
        close(db);
    }
}

public boolean update(Test t) {
    try {
        db = openWrite(DataSource.getInstance(mContext));
        ContentValues values = new ContentValues();
        int count = -1;
        if (db != null) {
            db.beginTransaction();
            values.put(SQLConstants.KEY_TYPE, t.getType());
            values.put(SQLConstants.KEY_DATE, t.getDate());
            count = db.update(SQLConstants.TEST_TABLE_NAME, values, SQLConstants.KEY_ID + "= ?", new String[] {t.getId()});
            db.setTransactionSuccessful();
        }
        return count > 0;
    }
    finally {
        if (db != null) {
            db.endTransaction();
            close(db);
        }
    }
}


public boolean delete(Test t) {
    int count = -1;
    try {
        db = openWrite(DataSource.getInstance(mContext));
        if (db != null) {
            db.beginTransaction();
            count = db.delete(SQLConstants.TEST_TABLE_NAME, SQLConstants.KEY_ID + "= ?", new String[] {t.getId()});
            db.setTransactionSuccessful();
        }
        return count > 0;
    }
    finally {
        if (db != null)
            db.endTransaction();
            close(db);
        }
    }
}

public List<Test> getAll() {
    Cursor c = null;
    List<Test> tests = new ArrayList<Test>();
    Test test = null;
    try {
        db = openRead(DataSource.getInstance(mContext));
        if (db != null) {
            String[] columns = {SQLConstants.KEY_ID, SQLConstants.KEY_TYPE, SQLConstants.KEY_DATE};
            c = db.query(SQLConstants.TEST_TABLE_NAME, columns, null, null, null, null, null);
            if (c.moveToFirst()) {
                do {
                    test = new Test();
                    test.setId(c.getInt(c.getColumnIndex(SQLConstants.KEY_ID)));
                    test.setType(c.getString(c.getColumnIndex(SQLConstants.KEY_TYPE)));
                    test.setDate(c.getString(c.getColumnIndex(SQLConstants.KEY_DATE)));
                    tests.add(test);        
                } while(c.moveToNext());
            }
        }
        return tests;
    }
    finally {
        if (c != null) {
            c.close();
        }
        if (db != null) {
            close(db);
        }
    }
}

private final synchronized SQLiteDatabase openWrite(SQLiteOpenHelper handler) {
    return handler.getWritableDatabase();
}

private final synchronized SQLiteDatabase openRead(SQLiteOpenHelper handler) {
    return handler.getReadableDatabase();
}

private final synchronized void close(SQLiteDatabase db) {
    if (db != null && db.isOpen()) {
        db.close();
    }
}
}

So basic example of implementation that can be modified / improved whenever based on individual requirements. And if you want to make implementation " on more abstract level" so DAOFactory design pattern should be the best choice.

Note: Is very useful to gain experience from other Android developer(s) and then start thinking about it and decide suitable approach.