Guid versus ID

[Mike Hildner]

I'm working on normalizing a database, and finding that I'm doing a lot of second normal form stuff, currently there are lots of tables that have what should be a record in another table flattened within itself.

I guess one would normally have (for example) a Vehicle_ID field that pointed to an auto-incrementing ID in the Vehicles table.

I'm pretty well convinced that the auto-increment ID won't work for us, because we will have disconnected applications, so I've been using guids. Is there anything to watch out for? I'm especially concerned about the best way to handle unique constraints, clustered indexes and how JOIN statements might be affected.

Any thoughts appreciated,
Mike

[Ecniv]

I guess one would normally have (for example) a Vehicle_ID field that pointed to an auto-incrementing ID in the Vehicles table

Well no... You can use your own code to make up numbers as long as they are unique, you shouldn't have a problem.

If you are getting data from several apps, perhaps it would be best to get a complete list of all options (clean it up as well I guess) and then when importing you assign the ID from the complete list against the text in the records.

I've just re-read that and it sounds confusing... So I'll try to write an example

App1
01 - Car
02 - Truck
03 - Van

App2
01 - Van
04 - Car
06 - Lorry

YourNewDB
01 - Car
02 - Truck
03 - Van
04 - Lorry

Table of data incoming:
blah blah Car blah blah
blah blah Van blah blah
blah blah Lorry blah blah

Changes to:
blah blah 01 blah blah
blah blah 03 blah blah
blah blah 04 blah blah


Now if you need to get updates of info (because life is never easy) you may want to have a lookup list from the old values to the new (if ids are held in the old/other app) then you can use that insted to ensure your data is correct.


Just a thought.