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