Database: Sharing two sets of values in a single table?

Question

I'll start by saying I am not a DBA and I didn't get to do heavy database development so far (so I hope I'm not asking something obvious).

The Challenge:

1. I have a dictionary application with pre-defined values.
2. New values may be added via online updates.
3. Users are not allowed to modify these application-values, but they may add/delete values of their own.
4. The database (sqlite3) will contain a small amount of values (~2K-3K).
5. The database schema is exactly the same for both user and application values.

Possible solutions:

• One way to go about it would be to create two different tables having the same schema, and JOIN the data from both tables when querying the database.

• A different approach would be to have a single table in which application-values will start at ID=0, and user-values will start at ID=100000 (for example). Online updates will merge new values below ID=100000 such that user values will remain intact.

I prefer the second solution - it'll avoid JOINs during runtime and the queries will remain simple.
However, an update to the application-values in the first solution would require me to just replace the application table with the new one.

Please let me know what you think:

• Which solution is better?
• What are the pros/cons that I'm missing?
• Is there an even-better third solution?

Answer 1

Why not just a column 'type' to your table and fill it with user/application?

Personaly I hate meaningfull ID's....