SQL interface like pattern?

Question

I have two tables, SyncedComments and QueuedComments, the latter holds local comments until they are synced with a webserver, when they are synced succesfully they get placed in the synced table, my application should be indifferent to each type. I load in the comments through a CursorLoader, and they may be moved to the synced table while users are reading them. Let's say the user can also edit comments, perhaps while they are being moved, so the application should know where the comment is, regardless of it's table.

To support this, I've thought of having a table with 3 columns, local_id, synced_id and queued_id, the local_id is persistent and simply serves as a reference to either one of the two other id's. When a comment is created a new row is inserted with it's sync_id set to NULL and the queue id it's been given, when a comment is moved then the queue_id is set to NULL and the sync_id is set. This way my application only needs to reference the local id at all times.

How does this solution look? Any flaws? Could it be done smarter?

Answer 1

I would in the first place put all the comments in one table, with flag for whether the comment is synchronized (actually it would probably be ID on server, set to NULL until synchronized and the value obtained from server afterwards). That will take you down to 1 table instead of 3, make it easier to show all comments (because you won't need to do union) and above all avoid problems when the comment is synchronized while being shown, because the comment will not be moving anywhere. And it does less writes to the database file, so it causes less fragmentation and fewer writes to the flash device.

• soren.qvist: Thanks for the answer. This would have a few implications with regards to table keys, I need to be able to fetch each comment individually and without a key
• Jan Hudec: I don't see what you mean. You can fetch the comments in the same ways whether you have them in one table or split over two tables. The queries will basically look the same.
• soren.qvist: Right but lets say I want to do an update on a comment with a NULL id, I can't do that
• Jan Hudec: @soren.qvist: If the comment isn't synchronized, you will have NULL server id, but you will still have the local id filled in. And you always do update by local id, because that's the stable one.
• soren.qvist: Okay I understand now, that would certainly make it possible, thanks again!