 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / Languages / C# / March 2008Generating unique incremental integer ID for database tables
Hi, I can imagine this question has been brought up before but I've spent all morning trying to find what I need on google without success. My application sits on Mysql or MS sql server engines and to date I've just been using auto-incremental int64 fields to generate my RecID field. I know that in the future the database will need to support multiple sites, so theres also a SiteID field in there too. example table ABC Primary Key = (int64)ABC.RecID + (int32)ABC.fk_Sites_RecID In the not too distant future multi sites WILL be needed, so I need to switch out auto-incrementation and generate record ids myself so that the sites can be synchronised across to a central server (another noob terror to come) I don't want to use triggers or stored procedures as I want my database to be as simple/clean as possible. How should I generate my "autoincremental" unique RecIDs please? thank you > Hi, > I can imagine this question has been brought up before but I've spent all [quoted text clipped - 17 lines] > > thank you Look into UUID >> How should I generate my "autoincremental" unique RecIDs please? > > Look into UUID Thank you for the UUID suggestion :) I have to stick with my incremental big ints as they're more likely to be compatible over a selection of different servers than guid/uuids. (ie the version of MySQL I'm using doesnt support them) Claire > >> How should I generate my "autoincremental" unique RecIDs please? > [quoted text clipped - 6 lines] > > Claire Anything will support UUID - because you can always store it as a string. Generate new UUID in your script/program and pass it to your SQL... There is a problem with UUID, though. They're huge, especially when converted to a string form. >> >> How should I generate my "autoincremental" unique RecIDs please? >> [quoted text clipped - 10 lines] > string. > Generate new UUID in your script/program and pass it to your SQL...  SignaturePosted via a free Usenet account from http://www.teranews.com On Mar 18, 1:21 pm, "Christopher Van Kirk" <chris.vank...@fdcjapan.com> wrote: > There is a problem with UUID, though. They're huge, especially when > converted to a string form. <snip> http://en.wikipedia.org/wiki/UUID <extract> In its canonical form, a UUID consists of 32 hexadecimal digits, displayed in 5 groups separated by hyphens, in the form 8-4-4-4-12 for a total of 36 characters... Having a primary key that is the sum of two integers could get you in a lot of trouble, since there are many combinations of two integers that could add up to the same number. But in general, you would need to get the SQL MAX(ID) from the table, increment by 1, and that would be your next id integer. -- Peter Site: http://www.eggheadcafe.com UnBlog: http://petesbloggerama.blogspot.com Short Urls & more: http://ittyurl.net > Hi, > I can imagine this question has been brought up before but I've spent all [quoted text clipped - 17 lines] > > thank you No, Im sorry if my shorthand is wrong Peter I meant there are 2 fields in the primary key. (Im not daft enough to add the values together) 1) 64 bit integer RecID field 2) 32 bit integer fk_Sites_RecID (foreign key, Sites table, RecID field) but thank you for the MAX(ID) suggestion :) Claire > Having a primary key that is the sum of two integers could get you in a > lot [quoted text clipped - 3 lines] > MAX(ID) > from the table, increment by 1, and that would be your next id integer. (inline comments) > example table ABC > Primary Key = (int64)ABC.RecID + (int32)ABC.fk_Sites_RecID [quoted text clipped - 3 lines] > the sites can be synchronised across to a central server (another > noob terror to come) I don't really follow that. If each site uses a different SiteID, then the same RecID value can occur at multiple sites, no? That would seem to be the point of including the SiteID in the primary key in the first place. > I don't want to use triggers or stored procedures as I want my > database to be as simple/clean as possible. That would not be my definition of a simple and clean database, but to each her own... > How should I generate my "autoincremental" unique RecIDs please? I would be tempted to create a single column primary key out of RecID, since they are so useful in code. Can you set a starting value for the auto-increment of each table at each site? If you assumed that you would have less than 10 million sites, and each site would have less than 10 billion records per table, you could set the starting value at each site, using offsets of 10 billion, such that they would never overlap. Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.