Update part of database on app update

I'm developing an application with C++ (Qt) and SQLite. The app is installed with predefined database filled with some records. User has the ability to extend the database with his own records. I'm wondering how to achieve update of the app, i.e. when new version of app is being installed, I want to remove the old core (predefined) records in the database, and replace with (insert) the new ones. These questions arise:

• Should I use 2 databases with similar schema, one modifiable for user and second read-only predefined one (so the app update would be just copying new predefined database over old one)?
• Or should I have a column in database about the author (app or u开发者_开发百科ser), and somehow (and how?) on update delete old app records and insert new ones?

Any ideas? Thanks

There's a third variant:

• Keep your original tables as you need them for the app.
• For every table: Add a second table that consists of just one column.
• These columns reference the PKs of your 'real' tables.
• Then insert the keys of your default records in the new tables.
• During the update, just join the corresponding tables and delete, then insert again.

This greatly simplifies queries (compared to the first variant) and you don't have the extra column which you don't need for most of the records (I think).

If you don't want the extra tables, I'd say do the second variant. The first one would force you to do your queries twice and UNION them. That just doesn't sound right to me.

Good question. I would go for the second option, since we did something similar here.

Another possibility would be to classify the data in the database, so you could know if the record is part of the system or is from the user (by using keywords or range of values, for example). Although this solution does not require additional tables or columns, it is much harder to understand and maintain, since it gives hidden meaning to the database records.