samedi 14 mars 2015

What is the correct way to layout this sql lite database?

Failing to pay attention in database class is now catching up with me as I try to design my own project. I'm trying to use databases in Android.


Let's say I have a database with two tables. The first is like this:



_id(Primary Key) || Type(Foreign Key) || Cost || Size || Date


_id is auto-incrementing, and I never display it.


The next table is like this:



Type(Primary Key) || Size


The first table is named DATABASE_TABLE, and the second is named DATABASE_EXTENDED.


Now when I add things to my first table, I also add the corresponding values to my second table. When I query my first table, it shows up correctly. When I query the second table, it doesn't show different types of the size option.


Example: If I added:



Type || Size
____________
King || Full
King || Half


and queried table 2, it would only return



King || Full


I've tried designating the size as a foreign key, but it does nothing (and throws errors).


Can someone point me in the right direction?


Here are the appropriate code snippets:


Code for inserting everything:



public long insertRecord(String type, int cost, String size, String date){
ContentValues values = new ContentValues();
ContentValues others = new ContentValues();
values.put(KEY_TYPE, type);
values.put(KEY_COST, cost);
values.put(KEY_SIZE, size);
values.put(KEY_DATE, date);
others.put(KEY_TYPE, type);
others.put(KEY_SIZE, size);
db.insert(DATABASE_EXTENDED, null, others);
return db.insert(DATABASE_TABLE, null, values);
}


Code for querying everything:



public Cursor getAllTitles() {

return db.query(DATABASE_TABLE, new String[]{
KEY_TYPE,
KEY_COST,
KEY_SIZE,
KEY_DATE},
null,
null,
null,
null,
null);

}


And the code for querying the second table:



public Cursor getAllTypes(){
return db.query(DATABASE_EXTENDED, new String[]{
KEY_TYPE,
KEY_SIZE},
null,
null,
null,
null,
null);
}


For last minute clarification, here is a sample:



type cost size date_installed
King 478 full 2/14/2015
Queen 478 half 2/14/2015
King 478 half 2/14/2015


and if I queried the second table, it would return:



type size
King full
Queen half

Aucun commentaire:

Enregistrer un commentaire