Best Practice: Table Primary Key

  • Thread starter Thread starter a
  • Start date Start date
A

a

Hi gang,

I need some SOLID direction here.

I am planning to use DataSets and DataBinding in an app which will have a
few users (VB.NET / SQL Server). I need to be able to create an 'offline'
mode, where they do not have access to the WebService, and would therfore
need to store the data locally as XML, then merge into the DB later.

My issue relates to primary keys. As a former Access Guy (current app is
SQL Server 2000), I have grown accustomed to the use of Auto-Incrementing
primary keys for a table, then making relationships to this integer value.
In .NET Datasets, I know (painfully) that I would need to set the
AutoincrementStep and AutoincrementSeed of the dataset to -1 and 0, to avoid
issues. This doesn't work for me here, since I need the PrimaryKeys to be
the same in the 'offline' xml file and the online DB.

I am willing to change my ways, and begin to use either 'natural' keys, or
GUID fields. I would like some people to share their experience on using
either of these is similar situations. Are there performance hits
asssociated with relating tables to VARCHAR fields as oposed to INTs? Are
there any trade-offs I need to be aware of prior to using this approach?
Should I 'never use an AutoIncrement again'? Does databinding have any
issues using a GUID as the Value of a combobox, for example?


Thanks!!

Kevin
 
You have a couple of choices, as I see it.

1. Do not create keys on the client side and allow the database to solve
that when you send data back. This gets increasingly difficult when you deal
with multiple tables offline.

2. Use GUIDs, which are a bit clunkier, but guarantee uniqueness. If you
really have to have unique, this is a good method.

3. Use MSDE on the client and work up replication between client and server.
This is not the greatest offline solution, however. You could, however, make
a sync utility and add a certain amount of numbers to the MSDE database when
you attempt a sync and then have the MSDE autoincrement, but sync at the
next higher number in SQL Server. This may get difficult in some situations.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************
 
Hi Kevin,

what you could do is reserve like a thousand keys for each table per user
and use the AutoIncrementSeed to specify a starting point.

Robert
 
Lets assume I have only one user. She adds records 1 through 10. Later,
she deletes the record with ID = 10. Tomorrow, she gets the dataset from
the server, containing ID's 1 through 9. IF I don't set the
AutoIncrementStep and AutoIncrementSeed in the dataset, when she adds a new
record to the dataset, the new record will get ID = 10. This then creates
issues on updates, where the saved record has ID = 11 in the DB.

If I fix this issue with the MS Suggestion of setting AutoIncrementStep
to -1 and AutoIncrementSeed to 0 , I avoid the Primary Key collisions.
However, I introduce a new issue. When the user goes 'offline', and saves
the Dataset to an XML file, the ID's of new records are negative. If she
then merges back to the server, the ID's will be positive, and all is well.

Wait! Is this not really an issue? Can this handle multiple users, too?

Gee, I think you are right. No Issue.

Thanks!

Kevin


Miha Markic said:
Hi a,

I am not sure why AutoInc keys are not good for you?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi gang,

I need some SOLID direction here.

I am planning to use DataSets and DataBinding in an app which will have a
few users (VB.NET / SQL Server). I need to be able to create an 'offline'
mode, where they do not have access to the WebService, and would therfore
need to store the data locally as XML, then merge into the DB later.

My issue relates to primary keys. As a former Access Guy (current app is
SQL Server 2000), I have grown accustomed to the use of Auto-Incrementing
primary keys for a table, then making relationships to this integer value.
In .NET Datasets, I know (painfully) that I would need to set the
AutoincrementStep and AutoincrementSeed of the dataset to -1 and 0, to avoid
issues. This doesn't work for me here, since I need the PrimaryKeys to be
the same in the 'offline' xml file and the online DB.

I am willing to change my ways, and begin to use either 'natural' keys, or
GUID fields. I would like some people to share their experience on using
either of these is similar situations. Are there performance hits
asssociated with relating tables to VARCHAR fields as oposed to INTs? Are
there any trade-offs I need to be aware of prior to using this approach?
Should I 'never use an AutoIncrement again'? Does databinding have any
issues using a GUID as the Value of a combobox, for example?


Thanks!!

Kevin
Click to expand...
Click to expand...
 
Set the AutoIncrementValue to -1. That resolves all of this b/c the db will
automatically assign the value since negative numbers aren't valid

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp
a said:
Lets assume I have only one user. She adds records 1 through 10. Later,
she deletes the record with ID = 10. Tomorrow, she gets the dataset from
the server, containing ID's 1 through 9. IF I don't set the
AutoIncrementStep and AutoIncrementSeed in the dataset, when she adds a new
record to the dataset, the new record will get ID = 10. This then creates
issues on updates, where the saved record has ID = 11 in the DB.

If I fix this issue with the MS Suggestion of setting AutoIncrementStep
to -1 and AutoIncrementSeed to 0 , I avoid the Primary Key collisions.
However, I introduce a new issue. When the user goes 'offline', and saves
the Dataset to an XML file, the ID's of new records are negative. If she
then merges back to the server, the ID's will be positive, and all is well.

Wait! Is this not really an issue? Can this handle multiple users, too?

Gee, I think you are right. No Issue.

Thanks!

Kevin


Miha Markic said:
Hi a,

I am not sure why AutoInc keys are not good for you?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

Hi gang,

I need some SOLID direction here.

I am planning to use DataSets and DataBinding in an app which will
Click to expand...
Click to expand...
have
a
Click to expand...
to
be
Click to expand...
keys,
or
Click to expand...
Click to expand...
 
2. Use GUIDs, which are a bit clunkier, but guarantee uniqueness. If you
really have to have unique, this is a good method.
Click to expand...

From the documentation:
"A GUID is a 128-bit integer (16 bytes) that can be used across all
computers and networks wherever a unique identifier is required. Such an
identifier has a very low probability of being duplicated."

Keyword being low probability, it does not guarantee uniqueness. You can
either assume it does and except dataloss and/or corruption in the extremly
few cases they arent unique, or you have to handle the cases when you
generate the same key.

/Dan
 
Back
Top