Ask away

Tag schema for an android app

John 01/08/18 16:44


Question CategoryConfiguration,Miscellaneous

Number of completed projects0

Question StatusUnsolved

  • tag
  • schema
  • android


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?


Oscar 01/11/18 01:13

I'm thinking in deferent way! what about saying good bye to queries and start using ORM in android ( Green DAO or ORM lite

Jacqui 01/12/18 11:46

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.

Frankel 01/12/18 22:17

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

Doug 01/12/18 23:59

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.

Robert 01/13/18 11:28

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.