Android: ContentProvider for each table / handling one-to-many relationships

Question

When using a content provider for SQLite database access

1. Is it better practice to have a content provider for each table or to use one for all tables?
2. How to handle one-to-many relationships when creating new records?

Answer 1

A ContentProvider is not a database

A ContentProvider is a way to publicly (or semi-publicly) access data as content. This may be done in a number of ways, via file access, SQLite or even web access. A ContentProvider in and of itself is not a database, but you can program a database for it. You may also have multiple ContentProviders accessing the same database, but distributing different levels of access, or the same content in different ways according to the requestor.

What you are really asking is not a ContentProvider question, but a database question "How to handle relationships in an SQLite database" because the ContentProvider doesn't use any database code unless you tell it to via an SQLiteOpenHelper and other similar classes. So, you simply have to program your database access correctly and your SQLite database will work as desired.

A database is a database

In the old days, databases were simply flat files where each table was often its own entity to allow for growth. Now, with DBMS, there is very little reason to ever do that. SQLite is just like any other database platform in this regard and can house as many tables as you have space to hold them.

SQLite

There are certain features that SQLite handles well, some that it handles - but not well, and some that it does not handle at all. Relationships are one of those things that were left out of some versions of Android's SQLite, because it shipped without foreign key support. This was a highly requested feature and it was added in SQLite 3.6.22 which didn't ship until Android 2.2. There are still many reported bugs with it, however, in its earliest incarnations.

Android pre 2.2

Thankfully being SQL compliant and a simple DBMS (not RDBMS at this time), there are some easy ways to work around this, after all, a foreign key is just a field in another table.

1. You can enforce database INSERT and UPDATE statements by creating CONSTRAINTs when you use your CREATE TABLE statement.
2. You can query the other table for the appropriate _id to get your foreign key.
3. You can query your source table with any appropriate SELECT statement using an INNER JOIN, thus enforcing a pseudo-relationship.

Since Android's version of SQLite does not enforce relationships directly, if you wanted to CASCADE ON DELETE you would have to do it manually. But this can be done via another simple SQL statement. I have essentially written my own library to enforce these kinds of relationships, as it all must be done manually. I must say, however, the efficiency of SQLite and SQL as a whole makes this very quick and easy.

In essence, the process for any enforced relationship goes as follows:

• In a query that requires a foreign key, use a JOIN.
• In an INSERT use a CONSTRAINT on the foreign key field of NOT NULL
• In an UPDATE on the primary key field that is a foreign key in another TABLE, run a second UPDATE on the related TABLE that has the foreign key. (CASCADE UPDATE)
• For a DELETE with the same parameters, do another DELETE with the where being foreign_key = _id (make sure you get the _id before you DELETE the row, first).

Android 2.2+

Foreign keys is supported, but is off by default. First you have to turn them on:

db.execSQL("PRAGMA foreign_keys=ON;");

Next you have to create the relationship TRIGGER. This is done when you create the TABLE, rather than a separate TRIGGER statement. See below:

// Added at the end of CREATE TABLE statement in the MANY table
FOREIGN KEY(foreign_key_name) REFERENCES one_table_name(primary_key_name)

For further information on SQLite and its capabilities, check out SQLite official site. This is important as you don't have all of the JOINs that you do in other RDBMS. For specific information on the SQLite classes in Android, read the documentation.