CODEWITHSUNDEEP
sqlsqlite
Pierre Rymiortz
Pierre Rymiortz

Reputation: 1127

Retrieve ordered list with items from SQLite database

I’m designing tables for a database.

Inside an Android app, I download - from a server - an ordered list in JSON. I insert the list and its items into a SQLite db.

The list table’s columns:

+ list_id
+ timestamp

The list item table’s column:

 + item_id
 + timestamp
 + list of list_ids (a list item can be in multiple lists)

When I retrieve the items for a list they should have the same order as when I downloaded them. What is the most efficient way to store the “list order"? One option is to let the item have a columns with a list of pair like so: list:indexInList. Appreciate advice on betters ways to do this.

Clarification: An item can occur in a list multiple times, e.g. item_ids in a list: 1,2,3,1,5.

Upvotes: 0

Views: 751

Answers (2)

CL.
CL.

Reputation: 180080

You should never store a list of values in a column when you might need to access individual items.

For a M:N relationship like this, you typically use a third table:

CREATE TABLE items_in_lists (
    list_id INTEGER REFERENCES lists(list_id),
    item_id INTEGER REFERENCES items(item_id),
    PRIMARY KEY(list_id, item_id)
);

Once you have this table, it is the natural place to store the item's order in the list.

Upvotes: 1

Misunderstood
Misunderstood

Reputation: 5665

If you have an id column with an auto-increment then add ORDER BY id to your query.

You can reorder your table. 

ALTER TABLE tbl_name ORDER BY col_name

Upvotes: 1

Related Questions