Designing a Core Data managed object model for an iOS app that creates dynamic databases

Question

I'm working on an iPhone app for users to create mini databases. The user can create a custom database schema and add columns with the standard data types (e.g. string, number, boolean) as well as other complex types such as objects and collections of a data type (e.g. an array of numbers).

For example, the user can create a database to record his meals.

Meal database:

[
  {
    "timestamp": "2013-03-01T13:00:00",
    "foods": [1, 2],
    "location": {
      "lat": 47.253603, 
      "lon": -122.442537
    }
  }
]

Meal-Food database:

[
  {
    "id": 1,
    "name": "Taco",
    "healthRating": 0.5
  },{
    "id": 2,
    "name": "Salad",
    "healthRating": 0.8
  }
]

What is the best way to implement a database for an app like this?

My current solution is to create the following database schema for the app:

Core Data Object Model

When the user creates a new database schema as in the example above, the definition table will look like this:

+----+-----------+--------------+------------+-----------------+
| id | parent_id |     name     | data_type  | collection_type |
+----+-----------+--------------+------------+-----------------+
|  1 |           | meal         | object     |                 |
|  2 |         1 | timestamp    | timestamp  |                 |
|  3 |         1 | foods        | collection | list            |
|  4 |         1 | location     | location   |                 |
|  5 |           | food         | object     |                 |
|  6 |         5 | name         | string     |                 |
|  7 |         5 | healthRating | number     |                 |
+----+-----------+--------------+------------+-----------------+

When the user populates the database, the record table will look like this:

+----+-----------+---------------+------------------------+-----------+-----+
| id | parent_id | definition_id |      string_value      | int_value | ... |
+----+-----------+---------------+------------------------+-----------+-----+
|  1 |           |             1 |                        |           |     |
|  2 |         1 |               | 2013-03-01T13:00:00    |           |     |
|  3 |         1 |             2 |                        |         1 |     |
|  4 |         1 |             2 |                        |         2 |     |
|  5 |         1 |             4 | 47.253603, -122.442537 |           |     |
+----+-----------+---------------+------------------------+-----------+-----+

More details about this approach:

Values for different data types are stored in different columns in the record table. It is up to the app to parse values correctly (e.g. converting timestamp int_value into a date object).
Constraints and validation must be performed on the app as it is not possible on the database level.

What are other drawbacks with this approach and are there better solutions?

Search re "EAV". Hint: "The user can create a custom database schema". So what do you call a *system* that *manages* a *data* *base*? So what thing is your application, along with whatever else it is? So how can you avoid writing code for that fuctionality? — philipxy, Dec 13 '14 at 00:01
Core Data is not a database, trying to use it as such will lead to the Pit Of Despair. — quellish, Dec 14 '14 at 21:33
@quellish I understand that Core Data acts like an abstract layer on top of SQLite (if it's chosen as the store). What are better alternatives to using Core Data with SQLite? — Benjamin Cheah, Dec 16 '14 at 08:00
@zxzxlch Core Data is an object graph management API. Using it as such yields the best results. SQLite is an implementation detail of a particular concrete store behind an abstract interface. — quellish, Dec 18 '14 at 17:50

score 0 · Answer 1 · edited May 23 '17 at 11:58

First of all your Record table is very inefficient and somewhat hard to work with. Instead you can have separate record tables for each record type you need to support.It will simplify everything a lot and add some additional flexibility, because it will not be a problem to introduce support for a new record type.

enter image description here

With that being said we can conclude it will be enough to have basic table management to make your system functional. Naturally, there is ALTER TABLE command:

enter image description here

but in some cases it might be very expensive and some engines have various limitations. For example:

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table.

Another approach might be to use BLOBs with some type tags in order to store record values.

enter image description here

This approach will reduce the need to support separate tables. It leads us to Schemaless approach.

Good idea to create separate tables for each record type, thank you. Schemaless databases sound interesting but are there any decent solutions for iOS? — Benjamin Cheah, Dec 16 '14 at 08:15
Schemaless is decent for any platform if it does solve the problem. I would say simplified design is more welcome on mobile platform. BTW, there is, for example, NanoStore solution (https://github.com/tciuro/NanoStore) which can help you to understand potentials benefits. — Renat Gilmanov, Dec 19 '14 at 13:49

score 0 · Answer 2 · answered Dec 18 '14 at 11:54

0

Do you absolutely have to use CoreData for this? It might make more sense to use a schema-less solution, such as http://developer.couchbase.com/mobile/develop/references/couchbase-lite/release-notes/iOS/index.html

answered Dec 18 '14 at 11:54

Jacek Lampart

1,741
13
25

Designing a Core Data managed object model for an iOS app that creates dynamic databases

2 Answers2