Default database IDs; system and user values

Question

Hi

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>

...And...

5, <user template>

Then.. we issue an upgrade.. and insert as part of our upgrade scripts...

<new id> [6], <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?

Thanks, Duncan

Answer 1

Maybe I didn't get it, but couldn't you use GUIDs as Ids and still have user and system data together? Then you can access the system data by the (non-changable) GUIDs.

Answer 2

I recommend using the second with the modification that you store the system and user values in one table. 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.

Answer 3

Yes we could, but personally I would prefer to stay away from GUIDs, as this brings in a whole host of maintenance issues.

I would imagine that it would make development A LOT more painful as well, in terms of debug, unit-testing etc.

Unless my concerns are misplaced?

Answer 4

I don't think that GUID should make any problem.

If you want to avoid it, then use a flag:

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.

Answer 5

+1 for Biri's text based ID - define a "template_mnemonic" text based column and make it the primary key. 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.

Answer 6

Hi Duncan,

I have never had problems (performance or development - TDD & unit testing included) using GUIDs as the ID for my databases, and I've worked on some pretty big ones. 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 :-)

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. 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).

I hope that helps,

Rob G

Answer 7

Thanks for the responses, to be honest I was surprised - I didn't think using GUIDs would be so favoured. I would still imagine that using them in day-to-day development could be a bit of a hassle.

To be honest, they are overkill for our project at the moment - we will be able to use ranges to meet our requirement, but I will consider using GUIDs in the future if it looks like we will benefit from their use.

Answer 8

I think there is a better third solution. 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)