Using multiple tables of the same database in Android

Question

I have an application that currently works fine. It is basically an exercise application where all the user's exercise entries are kept in a table. The issue is, at the moment this application only works for one user, and I would like to make it work for multiple users, but for this, I will need one separate exercise database table for each user. How could I do this?

I don't know if it's relevant, but here are the important parts my current database code:

private static final String USER_TABLE_CREATE =
    "create table if not exists UserProfile ( id integer primary key autoincrement,"
    + "age VARCHAR not null, weight VARCHAR not null, height VARCHAR not null, sex VARCHAR not null);";

private static final String RUNNER_TABLE_CREATE =        
        "create table if not exists RunnerHistoryTable ( id integer primary key autoincrement,"
        + "recommendation VARCHAR , activitydate VARCHAR , activity_distance VARCHAR , activity_time VARCHAR, activity_velocity VARCHAR, monitor VARCHAR, calories VARCHAR);";
private static final String WALKER_TABLE_CREATE =        
        "create table if not exists WalkerHistoryTable ( id integer primary key autoincrement,"
        + "recommendation VARCHAR , activitydate VARCHAR , activity_distance VARCHAR , activity_time VARCHAR, activity_velocity VARCHAR, monitor VARCHAR, calories VARCHAR);";
private static final String WEIGHT_LOSS_TABLE_CREATE =        
        "create table if not exists WeightLossHistoryTable (id integer primary key autoincrement, "
        + " recommendation VARCHAR , activitydate VARCHAR, activity_distance VARCHAR , activity_time VARCHAR, activity_velocity VARCHAR, monitor VARCHAR, calories VARCHAR);";

private final Context context;    

private DatabaseHelper DBHelper;
private SQLiteDatabase db;

public DatabaseAdapter(Context ctx) 
{
    this.context = ctx;
    DBHelper = new DatabaseHelper(context);
}

private static class DatabaseHelper extends SQLiteOpenHelper 
{
    DatabaseHelper(Context context) 
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) 
    {
        try {
            db.execSQL(USER_TABLE_CREATE);
            db.execSQL(RUNNER_TABLE_CREATE);
            db.execSQL(WALKER_TABLE_CREATE);
            db.execSQL(WEIGHT_LOSS_TABLE_CREATE);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
 }

//---insert a record into the database---

public long insertUser(String age, String weight, String height,String sex){
    ContentValues initialValues = new ContentValues();

    initialValues.put(AGE,age);
    initialValues.put(WEIGHT,weight);
    initialValues.put(HEIGHT,height);
    initialValues.put(SEX, sex);

    return db.insert(USER_PROFILE_TABLE, null, initialValues);
}

public long insertActivity(String tableName, String recommendation, String activityDate, String activityDistance, String activityTime, String activityVelocity, String monitor, String calories){
    ContentValues initialValues = new ContentValues();

    initialValues.put(RECOMMENDATION,recommendation);
    initialValues.put(ACTIVITY_DATE,activityDate);
    initialValues.put(ACTIVITY_DISTANCE,activityDistance);
    initialValues.put(ACTIVITY_TIME, activityTime);
    initialValues.put(ACTIVITY_VELOCITY, activityVelocity);
    initialValues.put(MONITOR, monitor);
    initialValues.put(CALORIES, calories);

    return db.insert(tableName, null, initialValues);   
}

score 1 · Answer 1 · answered May 23 '14 at 16:54

Your database should just be two tables, one to store users and one to store activities (add an activitytype column to your Activities table). Then you need to tie the user to the activities using a foreign key constraint.

private static final String USER_TABLE_CREATE =
    "create table if not exists Users ( id integer primary key autoincrement,"
    + "age text not null, weight text not null, height text not null, sex text not null);";

private static final String ACTIVITIES_TABLE_CREATE =        
    "create table if not exists Activities ( id integer primary key autoincrement,"
    + "userid int, activitytype text, recommendation text, activitydate text, activity_distance text,"
    + "activity_time text, activity_velocity text, monitor text, calories text,"
    + "FOREIGN KEY(userid) REFERENCES Users(id));";

Using multiple tables of the same database in Android

1 Answers1