I'll begin by saying I'm not a DBA and that i did not reach do heavy database development to date (and so i hope I am not asking something apparent).

The Task:

  1. I've got a dictionary application with pre-defined values.
  2. New values might be added via online updates.
  3. Customers aren't permitted to change these application-values, however they will addOrremove values that belongs to them.
  4. The database (sqlite3) will contain a tiny bit of values (~2K-3K).
  5. The database schema is the same for user and application values.

Possible solutions:

  • One method to do it is always to create two different tables getting exactly the same schema, and JOIN the information from both tables when querying the database.

  • Another approach is always to possess a single table by which application-values will begin at ID=, and user-values will begin at ID=100000 (for instance). Online updates will merge new values below ID=100000 so that user values will stay intact.

I favor the 2nd solution - it'll avoid JOINs throughout runtime and also the queries will stay simple.
However, an update towards the application-values within the first solution would require me to simply replace the applying table using the brand new one.

Please allow me to read your comments:

  • Which option would be better?
  • Do you know the pros/cons that I am missing?
  • Can there be a level-better third solution?

Thank you for reading through (!)

Why not only a column 'type' for your table and grow it with user/application?

Personaly I personally don't like meaningfull ID's....