How to structure complex Android database

Question

What are the best practices to structure big android databases? From what I've seen so far doing some research on this topic, I've seen that most people are using a DatabaseHelper, which extends SQLiteOpenHelper. Does this also apply when you have multiple tables?

For example, in my current project, I have about 30 tables. So far, all of my tables are being created in my DatabaseHelper class, which currently is getting pretty huge. For every module, I also have a (class)Source class, which has all the CRUD methods in that module, and is using a singleton instance of my DatabaseHelper class.

Somehow it feels unnatural to have all of that code in my DatabaseHelper class. Have I done it the right way, or are there some better way to do this?

score 2 · Accepted Answer · edited May 23 '17 at 12:06

2

I would recommend to produce the database via an DB-Client like Squirell or something else. Then you can deploy the database to your app via the assets folder of your app.

Have a look at this thread

edited May 23 '17 at 12:06

Community

1
1

answered Nov 18 '13 at 14:29

ZeusNet

710
9
25

score 0 · Answer 2 · answered Nov 18 '13 at 23:37

I've taken the following approach which helps separate responsibility. First off, start with an abstract database adapter. Its responsibility is to manage access to the database, create tables (if required), handle upgrades, etc. It provides no access to any tables.

public abstract class DBAdapter {
    private static final String DATABASE_NAME = "database.db";
    private static final int DATABASE_VERSION = 1;
    protected final Context context;
    protected SQLiteDatabase database;
    private DatabaseManager databaseManager;

    private static class DatabaseManager extends SQLiteOpenHelper
    {
        private static final String CREATE_TABLE_PERSON = "create table ...";
        //define other tables here

        DatabaseManager(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(CREATE_TABLE_PERSON);
            //create other tables here
        }
    }

    public DBAdapter(Context aContext)
    {
        context = aContext;
    }

    public DBAdapter open() throws SQLException {
        if (databaseManager == null)
        {
            databaseManager = new DatabaseManager(context);
        }

        database = databaseManager.getWritableDatabase();
        return this;
    }

    public void close() {
        databaseManager.close();
    }

}

Then, for each table, provide an implementation of your DBAdapter. It provides the access to your tables and the column definitions. Alternatively, you can also create DBAdapter implementations for each multi-table joins you need to do. This class provides the create/read/update/delete (CRUD) functionality for this type of access (table or multi-table join).

public class PersonAdapter extends DBAdapter
{
    static final String COLUMN__ID = "_id";

    static final String TABLE_PERSON = "Person";
    static final String COLUMN_DATE_OF_BIRTH = "DateOfBirth";
    static final String COLUMN_SURNAME = "Surname";
    //etc.

    public PersonAdapter(Context aContext) {
        super(aContext);
    }

    public long deletePerson(long personId)
    {
        open();
        long numberRowsDeleted = 
            database.delete(TABLE_PERSON, COLUMN__ID + "=" + personId, null);
        close();
        return numberRowsDeleted;
    }

    //your other Person CRUD methods go here
}

How to structure complex Android database

2 Answers2