current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm implementing a sortable listview using bauerca's library here. This works, but now I need to define some things where I need some help. This is the functionality of my app_

I can create up to 5 names. These names are saved in a SQLite database. Then, in other activity, these names are loaded from the database and shown to select which ones I'm going to use.

With 5 names, there are several combinations I can use, but the basic porpouse of this app is that when I select some names, these are shown in a sortable listview where I can sort them. After sorting them, they should be saved in another database to remember the position of each one.

For this, the second database has these columns: NAME, GROUP, ORDER.

My approach is this: if I select for example NAME1 and NAME2, this would be GROUP2_1. If I select NAME1 and NAME3 this would be GROUP2_2... and this with all the posible combinations. Then, in the ORDER column, the position of each name in the listview is saved.

But seems that this approach with the GROUP column doesn't work... it saves the position of each name, but regardless of the group to which it belongs. So for example, if I have these names:

MARK
PAUL
JERRY

If I select MARK and JERRY, the listview at first time would show them that way:

MARK
JERRY

But now I sort them:

JERRY
MARK

So these combination belongs to GROUP2_1 and JERRY has 1 value in the ORDER column and MARK the 2.

If now I go back and I select this other combination:

MARK
PAUL
JERRY

This should be GROUP3_1, and now I sort them this way:

MARK
JERRY
PAUL

So now MARK has ORDER value 1, JERRY 2 and PAUL 3.

Now if I go back and load again the first combination, it will load the names with the last asigned ORDER value so instead of loading in this order:

JERRY
MARK

It will load them using the values of the last combinations:

MARK
JERRY

Sorry for such a long explanation, but I dind't know other best way to explain my issue. In resume, the problem is that the ORDER value is asigned to each name, regardless of the GROUP, so this is not a valid approach for what I need to get.

Update -- key definition

Thats my current table:

db.execSQL("CREATE TABLE " + TABLE_NAME + " (" +
            TravelOrder._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
            TravelOrder.NAME + " TEXT NOT NULL " +
            ");");
share|improve this question

closed as unclear what you're asking by jwenting, GlenH7, MichaelT, Ampt, gnat Jun 18 at 7:04

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.
    
what is the key (key combination) in your table: NAME, GROUP, ORDER ? –  randomA Jun 17 at 8:02
    
@randomA excuse me but I don't understand your question –  masmic_87 Jun 17 at 8:04
    
It's not an answer, I am asking for clarification from you, so that's why it's here instead of in the answer box. I ask you what do you use as key for your second table? –  randomA Jun 17 at 8:06
    
@randomA I know is not an answer, just a mistake... I'm still learning to use databases so I'm not good at this... The database contains an _ID column wich is an autoincremented integer, and then the columns quoted above... I know that in the way I'm doing it, is the NAME column the one which has more importance, and it's asigning to each name a group and a order value... but I don't know how to do to get what I need –  masmic_87 Jun 17 at 8:11
    
Ok, I don't know why you don't understand my clarifying question. From what I can understand your question, you have a bigger problem than incorrect order. You might have the same name in one group having multiple order values, multiple names have the same order value, etc.. –  randomA Jun 17 at 8:43
show 1 more comment

1 Answer

up vote 1 down vote accepted

From what you have given here, I would make a few addition assumptions and then propose a solution.

  1. You have a set of entities which you can uniquely identify
  2. You care about the order (in the list) when you have lists of these entities
  3. An entity can be in multiple lists, lists are NOT all disjoined

Solution: Don't use generated ID in the second table. Instead create a table called group table which actually has generated ID. You will then have a relationship table between the group table and the initial entity table (name table): in this relationship table, use the combination of groupID and name as the key, also add in an order column (which you, and not the DB, will have to manage)

[EDIT:] Specifically speaking:

  • Table 1 (String name as key) to store the names.
  • Table 2 (int id as key) to store the id of the group/list
  • Table 3 relationship table between table 1 and table 2. This one will have the pair (group_id, name) as key and a column for order, you manage this value to keep an order of each name in the group.

If you know how many groups you will have (or how many groups will be added in a time range: hour, day, week, month, year), then I would propose another solution.

At the start of each interval, create the number of tables that you wanted. Each of the table will keep the list of names you want to have. Now you will use auto-generated Id whenever you add names into the table. There is a catch for this, but I am not sure if your usage case will run into it or not.

share|improve this answer
    
That's it! You've getted my problem. If I have understood you, you are proposing to instead of having an autogenerated ID in the second list as key, to have a value like Name-Group, is this right? About the amount of groups, it depends, I can have a maximun of 5 names, so this gives 26 possible combinations... But it depends of the user, if he just has 2 names, then the possible combinations number lowers to 10. About the columns, I define them in my ContentProvider class, in an inner class which extends BaseColumns. –  masmic_87 Jun 17 at 9:40
    
Ok, I'll try your approach, makes sense doing it that way... just what I need is to get a little bit more knowledge about databases –  masmic_87 Jun 17 at 10:09
    
If there is anything else, make a comment here, I will try to make correction to my suggestion for you. I hope my writing is not hard for you to understand. –  randomA Jun 17 at 10:16
    
Just to check, table 1 will just content NAME column as key. Table 2 will contain autogenerated ID as key and a GORUP column. And table 3 will contain NAME-GROUP column as key, and another column for ORDER. Just one doubt, as I've told you, I use a inner class implementing BaseColumns to define the columns of each database. This baseColumns implementation creates automatically the ID column which autogenerates an int. How do I modify this ID column? –  masmic_87 Jun 17 at 10:25
    
As I can understand it, you are correct. About the other inquiry: If it is an automatically generated column, why would you want to modify? –  randomA Jun 17 at 10:35
show 11 more comments

Not the answer you're looking for? Browse other questions tagged or ask your own question.