Designing a Managed Database Object Model for an iOS Application that Creates Dynamic Databases
I am working on an iPhone app for users to create mini databases. The user can create his own database schema and add columns with standard data types (for example, string, number, boolean), as well as other complex types such as objects and collections of data type (for example, an array of numbers).
For example, a user can create a database to record their meals.
Nutrition database:
[
{
"timestamp": "2013-03-01T13:00:00",
"foods": [1, 2],
"location": {
"lat": 47.253603,
"lon": -122.442537
}
}
]
Database "Food":
[
{
"id": 1,
"name": "Taco",
"healthRating": 0.5
},{
"id": 2,
"name": "Salad",
"healthRating": 0.8
}
]
What is the best way to implement a database for such an application?
My current solution is to create the following database schema for the application:
When the user creates a new database schema like 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 records 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 about this approach:
-
Values for different data types are stored in different columns in the records table. This application must parse the values correctly (for example, convert an int_value timestamp to a date object).
-
Constraints and validation must be done in the application as this is not possible at the database level.
What are the other disadvantages of this approach and are there any better solutions?
source to share
First of all, your table is Record
very inefficient and difficult to work with. Instead, you can have separate record tables for each record type you need to support. It will simplify everything and add additional flexibility, because it will not be a problem to implement support for a new post type.
With that said, we can conclude that this is enough to have basic table management to get your system up and running. Naturally, there is a command ALTER TABLE
:
but in some cases this can be very expensive and some engines have different 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 add a new column to an existing table.
Another approach could be to use BLOB with some type tags to store record values.
This approach will reduce the need to maintain separate tables. This brings us to the Principled Approach .
source to share
Do you need to use CoreData for this? It might make sense to use an unsuccessful solution like http://developer.couchbase.com/mobile/develop/references/couchbase-lite/release-notes/iOS/index.html
source to share