I want to use SQLite as file format for my app, a reporting program.
Below is an idea of the kind of schema I'll use (just the first few tables).
There will be one report per file and all tables are children of that. So far so good.
But I want to be able to merge files, eg. to store 100 reports in one file. I've read that with a little effort, I can use GUIDs in SQLite to avoid dealing with key conflicts when merging databases. Sounds good, but I need a GUID for every record in every table right? That's a lot of GUIDs for my statistics table which might be large!
I then read about multi-column primary keys.
Is there any way I can create just one GUID for a report, and have every child of the report incorporate that GUID into its key to make every record's key unique?
Is there a better overall approach?
Best Answer
It is not possible to have 'hidden' columns.
When you are using GUIDs to avoid merge conflicts, you must use GUID keys in every table that contains data to be merged. And when you already have a GUID, it does not make sense to add another column to the primary key.
If you do not want to use GUIDs, just deal with the conflicts, i.e., instead of blindly copying the records, map all old IDs to new ID values.