Tag schema for an android app

Question

I'm implementing tags in an android application, need some pointers on the following:

schema design for a user-configurable, multi-tag setup
how to optimise the schema for search, filtering and simplicity.

Note that I've already looked at some designs that focus on bigger server-like deployments, I'm looking for something that is simple and works for a single table in a mobile (SQLITE) environment.

On first thought, I'm thinking of using a character separated varchar to represent the associated tags like so #work#meeting#monthly. Any better way to design the same?

Some extra info : http://stackoverflow.com/questions/48475/database-design-for-tagging — Ravi Vyas, Sep 18 '13 at 19:16
@RaviVyas I'm thinking in deferent way! what about saying good bye to queries and start using ORM in android ( Green DAO or ORM lite) http://stackoverflow.com/questions/13680954/green-dao-vs-orm-lite-vs-active-android — LOG_TAG, Sep 19 '13 at 04:35

score 1 · Answer 1 · answered Sep 18 '13 at 07:36

1

If you use that approach you will have a problem searching and filtering, use a table for your data and another for your tags.

You can use a third table that gives the relation between data and tags, but it is slow and inefficient.

Now, for optimal performance for search and filter, use a list of pointers in the tags table to the data table, this way, if you filter by a tag you will get O(1) complexity. The problem is that you will get the tags related to a data slowly. You can do the same thing in reverse, and have the list of tags tied to your data, but you will have a lot of work to do to keep it valid, since you have to update both tag and data on update.

In the end, keeping in mind that nr_of_tags << nr_of_data you should use just the data pointer tied to the tag, and if you want to show the tags related to a data, then you parse that tags table and search.

Edit: just now I see you want to use just one table.

answered Sep 18 '13 at 07:36

Dragos Iordache

139
3

I really like the O(1) complexity of the filter, that is really great but I'm a little apprehensive of keeping the tags valid in two places, that is a bit of a concern. I also want the tags-associated-with-data lookup to happen really fast, as we will be doing that every time the list (with data) is scrolled. – Soham Sep 18 '13 at 08:04
Then I think it is best to keep the info in both tables. In the tag table keep the data associated with it, in the data table keep the tags associated with it. For every change you make, you will have to make 2 changes to the database, for this use begin transaction and end transaction(i think this is the way in sqlite) so if the app crashes mid comit, the change will not take place. – Dragos Iordache Sep 18 '13 at 13:08
Another approach is to read the tags from the start of the app, they should not be that many, so you have them in memory. From here you can easily find a suitable data structure for O(1) search for each data element. – Dragos Iordache Sep 18 '13 at 13:11

score 1 · Answer 2 · edited May 23 '17 at 12:00

I guess you'll have to make a tradeoff between offloading the processing to the db or doing it in your code.

I suggest doing it in your code as you'll avoid disk reads once you get the data in-memory and you can handle the processing at the application layer as efficiently as you want using threads etc.

This way you can avoid having to run SQLite itself in multi-threaded mode (thus having to take care of synchronization at the db layer) .

A very simple schema could be:

ID | TAGS
_________
1  | work,meeting,monthly
2  | home,leisure,yearly

You can store the array of string as comma separated values and retrieve them easily using a simple trick.

Then you can use standard java collections to map,sort etc

yup, this is also what I have in mind. The filtering then becomes O(n) which, I guess, is okay for me as it is not to frequent. — Soham, Sep 18 '13 at 08:06

Tag schema for an android app

2 Answers2