Android: Sqlite One-to-many Design

Anyone has good advise on how to implement one-to-many mapping for SQLite using ContentProvider? If you look at Uri ContentProvider#insert(Uri, ContentValues) you can see that it h

Solution 1:

I think you're looking at the wrong end of the one-to-many relationship.

Take a look at the ContactsContract content provider, for example. Contacts can have many email addresses, many phone numbers, etc. The way that is accomplished is by doing inserts/updates/deletes on the "many" side. To add a new phone number, you insert a new phone number, providing an ID of the contact for whom the phone number pertains.

You would do the same if you had a plain SQLite database with no content provider. One-to-many relationships in relational databases are achieved via inserts/updates/deletes on a table for the "many" side, each having a foreign key back to the "one" side.

Now, from an OO standpoint, this isn't ideal. You are welcome to create ORM-style wrapper objects (think Hibernate) that allow you to manipulate a collection of children from the "one" side. A sufficiently-intelligent collection class can then turn around and synchronize the "many" table to match. However, these aren't necessarily trivial to implement properly.

Solution 2:

You can use ContentProviderOperations for this.

They are basically bulk operations with the ability to back-reference to the identifiers generated for parent rows.

How ContentProviderOperations can be used for a one-to-many design is very well explained in this answer: What are the semantics of withValueBackReference?

Solution 3:

So I'm going to answer my own question. I was on the right track with having two tables and two model objects. What was missing and what confused me was that I wanted directly insert complex data through ContentProvider#insert in a single call. This is wrong. ContentProvider should create and maintain these two tables but decision on which table to use should be dictated by Uri parameter of ContentProvider#insert. It is very convenient to use ContentResolver and add methods such as "addFoo" to the model object. Such method would take ContentResolver parameter and at the end here are the sequence to insert a complex record:

1. Insert parent record through ContentProvider#insert and obtain record id
2. Per each child provide parent ID (foregn key) and use ContentProvider#insert with different Uri to insert child records

So the only remaining question is how to envelope the above code in transaction?