As part of our current database work, we are looking at a dealing with the process of updating databases.
A point which has been brought up recurrently, is that of dealing with system vs. user values; in our project user and system vals are stored together. For example...
We have a list of templates.
1, <system template> 2, <system template> 3, <system template>
These are mapped in the app to an enum (1, 2, 3)
Then a user comes in and adds...
4, <user template>
5, <user template>
Then.. we issue an upgrade.. and insert as part of our upgrade scripts...
<new id> , <new system template>
THEN!!... we find a bug in the new system template and need to update it... The problem is how? We cannot update record using ID6 (as we may have inserted it as 9, or 999, so we have to identify the record using some other mechanism)
So, we've come to two possible solutions for this.
In the red corner (speed)....
We simply start user Ids at 5000 (or some other value) and test data at 10000 (or some other value). This would allow us to make modifications to system values and test them up to the lower limit of the next ID range.
Advantage...Quick and easy to implement,
Disadvantage... could run out of values if we don't choose a big enough range!
In the blue corner (scalability)...
We store, system and user data separately, use GUIDs as Ids and merge the two lists using a view.
Advantage...Scalable..No limits w/regard to DB size.
Disadvantage.. More complicated to implement. (many to one updatable views etc.)
I plump squarely for the first option, but looking for some ammo to back me up!
Does anyone have any thoughts on these approaches, or even one(s) that we've missed?
How to filter by 2 fields when loading data into an access database table from an excel spreadsheet
Good strategy for leaving an audit trail/change history for DB applications?
To what extent should a developer learn database?
Have a look here, here and here if you want to find out more about using GUIDs (and the potential GOTCHAS involved) as your primary keys - but I can't recommend it highly enough since moving data around safely and DB synchronisation becomes as easy as brushing your teeth in the morning :-).
What is the “best” way to store international addresses in a database?
For your question above, I would either recommend a third column (if possible) that indicates whether or not the template is user or system based, or you can at the very least generate GUIDs for system templates as you insert them and keep a list of those on hand, so that if you need to update the template, you can just target that same GUID in your DEV, UAT and /or PRODUCTION databases without fear of overwriting other templates.
Stored Procedure and Timeout
The third column would come in handy though for selecting all system or user templates at will, without the need to seperate them into two tables (this is overkill IMHO)..
joining latest of various usermetadata tags to user rows
I hope that helps,.
Database: What is Multiversion Concurrency Control (MVCC) and who supports it? [closed]
What kind of database refactoring tools are there?
GUID is quite reliable in this manner.. Another idea: use any text-based ID (not necessary GUID), which you give for the system values and is generated by a random string or a string based on some kind of custom logic for the user values.. Another idea: use the first approach, but extend the table with a flag which shows if a value is system or user.
Maybe this is the easiest.
Ok, you have to write some kind of mechanism to update the correct system value, but it can be done easily..
This will be a known value when you insert it as you, the developers will have decided on it (or auto-generated it) and you will always be able to reference a template by its mnemonic regardless of how many user specified templates there are.
It also allows users to have a meaningful naming convention for their templates..
ID int. template whatever. flag enum/int/bool.Flag shows whether the actual value is a system or a user value.. If you would like to update a system value, then ask only for system values ordered by ID, and it will show you actual order of insertion (you should have a bigint or something for ID to make sure that it doesn't get full and it doesn't get the deleted IDs back to work).
With this list the x.
record is the x.
inserted system value..
It strikes me that you're storing two different things in the same table and that you might be better off creating 2 separate tables one for user templates and one for system templates.
You might then be able to create a view over the two tables to make them appear as a single object to your application.
Obviously I don't have full knowledge of your application and this may be impossible for you for any number of reasons but I think it's a neater solution than GUIDs and way safer than ranges of IDs (seriously don't do ID ranges it'll bite you one day).