How to store multiple data in SQLite Database using two tables?

Question

So, say there are two tables TABLE 1 and TABLE 2. TABLE 1 has two columns - Activity and Measurement. The Activity column has 3 items stored in it - Football, Soccer and Basketball. (For now let's not think about what's stored the Measurement column).

Now, what I want to do is, on the other table, TABLE 2, I want to have the three items in the Activity column, to be the columns of TABLE 2. Here's the layout of what I want it to feel like -

https://www.dropbox.com/sh/6ip5ipe8r4y25bx/AACp43UfpxI21wGg4y6nylUEa?dl=0

Sorry, for the dropbox link, apparently I cannot post images without having 10 or more reputations.

I am fairly new to Android Studio so I am having difficulties implementing the idea, and please let me know if there's any ways/ideas to implement it! Thanks!

*"I want to have the three items in the Activity column, to be the columns of TABLE 2"* That sounds like an awful table design. I suggest you think of a better solution, or expose the problem here so we can give a more adequate suggestion to your problem. — m0skit0, May 03 '15 at 17:27

score 1 · Accepted Answer · edited May 23 '17 at 12:28

1

I think it better to have a foreign key relationship rather than creating a new column every time because you will face a problem when adding a new column if previous column are not null-able .For your case I will create a table like this

Item Table

Activity Table

Data Table

I will use a Id of the Item table as a foreign key for both Activity Table and Data Table therefore you will not need to create a new column every time you decide to put new item. But if you don't want to use my recommended method,you can also try to change the database table with sql.Try reading this

edited May 23 '17 at 12:28

Community

1
1

answered May 03 '15 at 17:24

Sun Maung Oo

183
2
11

This is in my opinion a much better design for a relational DB than what OP is asking for. – m0skit0 May 03 '15 at 17:31
I definitely agree that this is a better design for DB. Generating an ID for each of the items is a much better approach. Thanks a lot! It never occurred to me to use the idea of foreign key. I will have to take a better look at that. Thanks! – user123378 May 03 '15 at 17:48

vovahost · Answer 2 · 2015-05-03T17:30:21.927

0

From your question it is hard to understand if you want just to create the structure you described like this:

String QUERY_CREATE_TABLE_1 = "CREATE TABLE Table1 + ( Activity, Measurement )";
String QUERY_CREATE_TABLE_2 = "CREATE TABLE Table2 + ( Football, Soccer,  Basketball )";

database.execSQL(QUERY_CREATE_TABLE_1);
database.execSQL(QUERY_CREATE_TABLE_2);

Or if you need a dynamic logic then it is better to use a different approach.
For every row inserted in Table1 create a new Table with activity name:

//called when database first created
public void createTable1() {
    String QUERY_CREATE_TABLE_1 = "CREATE TABLE Table1 + ( Activity, Measurement )";
    database.execSQL(QUERY_CREATE_TABLE_1);
}

public void addNewActivity(String activity, String measurement) {

    //Add new activity to Table 1
    ContentValues valuesTable1 = new ContentValues();
    valuesTable1.put("Activity", activity);
    valuesTable1.put("Measurement", measurement);
    database.insertOrThrow(TablesInfo.TABLE_NAME, null, valuesTablesInfo);

    //create A new Table with activity name
    String QUERY_CREATE_TABLE = "CREATE TABLE " + activity + " ( Name )";
    database.execSQL(QUERY_CREATE_TABLE);
}

A table for every activity

edited May 03 '15 at 17:30

answered May 03 '15 at 16:56

vovahost

34,185
17
113
116

This is a bad DB design. Let's say I want a query for all the teams of all activities listed in Table1, how would you write such a query? Or for example how would I get all teams that have measurement > 0? Such queries show how this design is flawed. I know OP is specifically asking this, but in this case OP has obviously chosen a wrong design and you should correct him/her on that. – m0skit0 May 03 '15 at 17:34
As I said his question wasn't very specific so I didn't consider different scenarios. If the user will provide more details I will edit my answer and maybe chane the approach. – vovahost May 03 '15 at 17:39
As I said, current approach is flawed and you should tell OP, even if he/she is asking for this way. With "A query for all the teams of all activities", I mean list a query that returns "Packers, Broncos, Bears, Manchester United, Arsenal, Chelsea , Bulls, Nicks, Spurs" (btw, it's "Knicks"). – m0skit0 May 03 '15 at 17:46
Yes, now I get it. @Lopht Ghost design is better for this purpose but we still don't know what use will be made of this database. – vovahost May 03 '15 at 17:51
Yeah originally, I was opting for this approach, but, Lopht Ghost gave a better suggestion to work with. Although, your idea of dynamic logic isn't bad but like m0skit0 mentioned, writing a query for that is actually tough and demonstrates a flawed design! – user123378 May 03 '15 at 17:52

How to store multiple data in SQLite Database using two tables?

2 Answers2