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?

Answer 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.

Answer 2

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