Need an ID that is unique across tables

[discocarp]

I'm new to ADO.Net so I'd like to present my plan and ask for feedback. Let me know if you have any better ideas or see any problems with this method.

 

I need to use an ID field (as the primary key) in multiple tables. This field must be unique in all tables, not just one particular table (due to constraints outside of my application and control), so I'm assuming I can't use an autonumber. My plan was to have a table in the database called "NextID" with a single record containing a single integer. All IDs in the database would be >=0. It is not necessary for the values to be sequential or neatly ordered.

 

As the application works with data, I'd use a series of negative numbers for the primary key on new records. When I update the database from my dataset (which would be for all new records since the last time the user "saved"), I'd know exactly how many unique IDs I'd need (ie: if the next "new" internal ID to be used was -67, I'd need 66 new real IDs). I'd start an exclusive transaction, get the next real ID for the database (saving it for internal use) then add 66 (or whatever) to it, update the database, end the transaction. Then I could update all the negative IDs internally, and update the database.

 

Is this a reasonable plan? The database may have a small number of concurrent users. I don't think the bottleneck of using an exclusive transaction for this would be a significant issue for updating a single value only occasionally when the user hit "save". Would you agree?

 

One other thing, the application must work across a multitude of database types (Access, MySql, SqlServer, and Oracle at a minimum, with potentially more to be added later). Is there any problem with this method on any of these varying database types (or other database types I haven't considered?).

 

Thanks.

 

discocarp

[Robby]

Create an ID field with uniqueidentifier data type and Is RowGUID and NOT NULL

[Nerseus]

I don't think the uniqueidentifier type is available in all databases (might be SQL Server only).

 

Your idea sounds good though one usually uses a transaction to increment the NextID field and use it for the insert. If you're sure no one will be using the database while you're performing the inserts, your idea of figuring out the number of IDs should work fine.

 

I don't think I'd use the ID value (-67 means 66 new IDs needed) though. If you delete a row from your DataSet and add another one, you'll be off by one. Not a big deal, you just might use up more IDs than necessary. Why not just get your table's rowcount, through: ds.Tables[0].Rows.Count.

 

-Nerseus

[discocarp]

I also thought the uniqueidentifier wasn't available in all databases, but I'm not sure.

 

I didn't state it above, but I was still planning on using a transaction for the row updates, but I was thinking this could be a shared transaction instead of an exclusive one. Are there any drawbacks to this? I was thinking only the nextid transaction would need to be exclusive.

 

I don't think its really a big deal if some IDs get "lost". If rows get deleted, ids will be lost, but I think the extra issues regarding concurrency would be a more complex issue to deal with than just losing a few IDs. I could always include a function to clean up the ids anyway, but with over 2 billion for just a 32 bit integer, I don't think its an issue. And I don't need them neatly ordered (they'll never even be displayed), I just need them to be unique across tables.

 

Thanks!

 

Peter

[Robby]

uniqueidentifier is SQL Server specific, Sorry Peter I should've read your post in its' entirety.

[JABE]

You can define a character field to hold a GUID value (36 characters, if I'm not mistaken), then generate GUID values from the client-side. Generating GUIDs is trivial in .NET: System.Guid.NewGuid().ToString.

[discocarp]

After reading more about GUID's, they seem to be an interesting alternative, but I have a couple of concerns. They "have a low probability of being repeated", but what happens if they are? I don't think this is even remotely likely, but the impact if this arose would be pretty severe wouldn't it?

 

Second, and probably more of a concern, is there a significant performance difference if I relate tables with these 36 byte strings instead of a simple 4 byte integer?

 

Peter

[Robby]

On a large scale app you will see a performance hit, Also, the 'low probability of being repeated' part...I would rely on it.

[Nerseus]

Personally, I like int values for keys whenever possible. If you ever need to look at data "in the raw", it's a major pain to match up a 32 character GUID (I've used them only on one project) :)

 

About transactions:

Normally, you should keep your transactions as small as required. Meaning, if you're doing a dataload it's often thought that all records must go in clean and thus the whole thing should be one transaction. But often you can get away with much smaller transactions. If you are dealing with an import type of application (rather than data entry), it's often a good idea to have a "scrubbing" area. Basically it's a set of tables that hold the imported data with a simple flag to indicate whether it's been read in clean or not. After a load, you can run a query to see how many bad records there are.

 

-Nerseus

[zelk]

I do not know if stored procedures is available in all databases but when I develop, I never let my code work directly against the database tables, only through stored procedures. I would do the following:

 

The stored procedure for inserting a record would look something like this (e.g. user table):

 

CREATE PROCEDURE p_user_insert

@first_name nvarchar(50),

@last_name nvarchar(50)

AS

@user_id int

EXEC p_get_unique_id @user_id

INSERT INTO

tbl_user

(

user_id,

first_name,

last_name

)

VALUES

(

@user_id,

@first_name

@last_name

)

 

The p_get_unique_id would work in an exclusive, very small transaction to read and increment a single value in a single row in a specific table, just like you described in the first post.

 

Am I missing anything with this approach?

 

/Ricky

[zelk]

oh, sorry... of course, as the last line in the stored procedure, you would have to run

EXEC p_user_select_by_key @@IDENTITY

 

so that your new record and ID is automatically returned and inserted into your dataset.

 

/Ricky