multiple table sqlite database

Question

I am new to SQlite database and I want to make 2 tables:

1) my_folders(
    folder_id       INTEGER PRIMARY KEY AUTOINCREMENT, 
    folder_tite     TEXT NOT NULL
    currency        TEXT 
    folder_descrip  TEXT
    folder_pic      TEXT
);

2) TABLE my_expenses(
    log_id      INTEGER PRIMARY KEY AUTOINCREMENT,
    e_folder_id INTEGER
    name        TEXT NOT NULL, 
    amount      INTEGER
    date        NUMERIC NOT NULL
    time        NUMERIC NOT NULL
    notes       TEXT
    FOREIGN KEY(e_folder_id) REFERENCES my_folders(folder_id)
);

My question is how to implement the 2nd table? Can I directly add the codes to create the 2nd table "my_expenses" in the same DBAdapter file? Or should I seperate all the tables in different files like in https://stackoverflow.com/a/5899110/1398267? If that's the case, what is the codes required to insert a new record in the table my_folders and my_expenses?

I already created 1 table and can add new record to the table through this piece of codes:

Button b1 = (Button) findViewById(R.id.bnext); 
b1.setOnClickListener(new OnClickListener() 
        { public void onClick(View v) 
            {
                db.open();
                db.insertFolder(getFolderTitle.getText().toString(), 
                        getCurrency.getSelectedItem().toString(), getFolderDescription.getText().toString());
                db.close();
            }
        }

---

Here is my database codes:

public class DBAdapter 
{
    public static final String KEY_ROWID = "_id";
    public static final String KEY_NAME = "name";
    public static final String KEY_CURRENCY = "currency";
    public static final String KEY_NOTES = "notes";
    private static final String TAG = "DBAdapter";

    private static final String DATABASE_NAME = "MyDB";
    private static final String DATABASE_TABLE = "my_folders";
    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_CREATE = 
            "create table my_folders (_id integer primary key autoincrement, "
            + "name text not null, currency text not null, notes text not null);";

    private final Context context;
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    public DBAdapter(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(DATABASE_CREATE);
            } 
            catch (SQLException e) 
            {
                e.printStackTrace();
            }
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
        {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS my_folders");
            onCreate(db);
        }
    }

    // ---opens the database---
    public DBAdapter open() throws SQLException 
    {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    // ---closes the database---
    public void close() 
    {
        DBHelper.close();
    }

    // ---insert a folder into the database---
    public long insertFolder(String name, String currency, String notes) 
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_NAME, name);
        initialValues.put(KEY_CURRENCY, currency);
        initialValues.put(KEY_NOTES, notes);
        return db.insert(DATABASE_TABLE, null, initialValues);
    }

    // ---deletes a particular folder---
    public boolean deleteFolder(long rowId) 
    {
        return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }

    // ---deletes all folders---
    public boolean deleteAll()
    {
        return db.delete(DATABASE_TABLE, "1", null) > 0;
    }

    // ---retrieves all the folders---
    public Cursor getAllFolders() 
    {
        return db.query(DATABASE_TABLE, new String[] 
                {KEY_ROWID, KEY_NAME, KEY_CURRENCY, KEY_NOTES}, null, null, null, null, null);
    }

    // ---retrieves a particular folder---
    public Cursor getFolder(long rowId) throws SQLException 
    {
        Cursor mCursor = db.query(true, DATABASE_TABLE, new String[] 
                { KEY_ROWID, KEY_NAME, KEY_CURRENCY, KEY_NOTES }, 
                KEY_ROWID + "=" + rowId, null, null, null, null, null);
        if (mCursor != null) 
        {
            mCursor.moveToFirst();
        }
        return mCursor;
    }

    // ---updates a folder---
    public boolean updateFolder(long rowId, String name, String currency, String notes) 
    {
        ContentValues args = new ContentValues();
        args.put(KEY_NAME, name);
        args.put(KEY_CURRENCY, currency);
        args.put(KEY_NOTES, notes);
        return db.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    }
}

Answer 1

Yes, you can add code in same file. First fetch the folder_id from my_folders that you want to add in your second table and store it in some class variable. You can use function which will return folder_id.

public int getFolder_id(String name) throws SQLException 
    {
       int id =0;
       Cursor cursor = db.rawQuery("select _id from my_folders where name  = ?", new String[] { name });

        if (mCursor != null) 
        {
            mCursor.moveToFirst();
            id = cursor.getInt(cursor.getColumnIndex("_id"));
        }
        return id;
    }

After that check if that variable is not 0 or -1 and then you can add similar code to add all the values in second table.

Answer 2

you have simply to execute more queries in your onCreate method:

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_DBINFO); //create table db_info as...
    db.execSQL(CREATE_TYPE); //create table ...
    db.execSQL(CREATE_TAG);  //create table ... all are constant strings!
    db.execSQL(CREATE_CATEGORY);
    db.execSQL(CREATE_RESOURCE);
    db.execSQL(CREATE_RESTAG);
    db.execSQL(CREATE_RESCAT);
    db.execSQL(CREATE_TYPECAT);
    db.execSQL("insert into DBInfo values(-1,0)");
    db.execSQL("insert into Tag values(1,'No tag',0)");
}

This is a short example from a project i did some time ago. Those are constants where the create table query is defined (exactly you already did for your first table).

Just write the new creation queries and you're good to go.