Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 18th, 2007, 09:49 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

hi there...

I can't think of a reason to do something like that... why create and delete 10000 records a day needing an ID column???
if the data is temp, then you have a better way to look for it for sure...
do you want to give me a quick explain of what are you trying to achieve???
Also access is not a good database if you will work it with several clients (doesn't work good in a network enviroment)...


HTH

Gonzalo
 
Old January 18th, 2007, 11:01 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

The only way you will achieve what you want, IMHO, is to A)Use just a number field for your ID (No auto inc or anything just data type INT) and B)write a Macro or such that will get the min value of your ID column and if the min value is > 1 then you know you can begin inserting rows that have small INT values.

That said, there is no EASY way to do what you want and Gonzalo makes a great point about the data being temp and being able to identifiy on some other column.

================================================== =========
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
================================================== =========
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile
================================================== =========
 
Old January 18th, 2007, 11:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

two things..

first: when I said me (do you want to give me a quick explain...) I wanted to say us... maybe doug and jeff have better ideas after knowing what are you trying to achieve...
second: Doug I love that doc!!! maybe all of us should use them in our signatures???



HTH

Gonzalo
 
Old January 18th, 2007, 11:57 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

Agreed it is a very good document that everyone should read ^^


================================================== =========
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
================================================== =========
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile
================================================== =========
 
Old January 19th, 2007, 12:54 PM
Friend of Wrox
 
Join Date: May 2006
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

There is a very easy way to do this... use a GUID for the primary key.

This is a standard method for doing this - it is in common use in the industry. There can possibly be some issues, but these are widely discussed in the literarture available on the web and it would be likely that any objection you have has been addressed.

I have worked on many projects where GUIDs were in use as primary keys and had no troubles I was aware of.

A GUID is a 128-bit integer that you can use across all computers and networks wherever a unique identifier is required, and you can pretty much never run out of them. This calculates to to 2^122 or 5,316,911,983,139,663,491,615,228,241,121,400,000 possible combinations. Most environments provide a method for generating GUIDs for use as primary key (or whatever).

Woody Z
http://www.learntoprogramnow.com
 
Old January 19th, 2007, 01:13 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by woodyz
 There is a very easy way to do this... use a GUID for the primary key.
The OP is using Access, and Access has a rather obscure way of declaring that a column contains a GUID (Column DataType is 'Number', and Field Size is 'ReplicationID').

Since the OP complained that "...[he] would still be identifying records with enormous integers..." when the 32 bit integer 'Autonumber' value got large in many many years, I sincerely doubt he would be happy with a GUID....

His issue is, I think, not so much a primary key one, as he wants to reuse some sort of integer identifier as rows come and go. There is no good way of doing this, at least no performant way (especially in Access) I can think of...

Jeff Mason
Custom Apps, Inc.
je.mason@comcast.net
 
Old January 19th, 2007, 01:46 PM
Friend of Wrox
 
Join Date: May 2006
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Jeff Mason

Since the OP complained that "...[he] would still be identifying records with enormous integers..." when the 32 bit integer 'Autonumber' value got large in many many years, I sincerely doubt he would be happy with a GUID....
The question would be is this an artificial constraint to be concerned with "enormous" integers?
Quote:
quote:
His issue is, I think, not so much a primary key one, as he wants to reuse some sort of integer identifier as rows come and go. There is no good way of doing this, at least no performant way (especially in Access) I can think of...
Well, performance is relative, and I haven't seen anything here that discusses a need for performance. 10000 records a day implies that there is not going to be any performance issue.

I think we can infer from the OP's comments that this is a single user application with no critical performance requirements. Assuming that a GUID is not acceptable, then maintaining a simple counter table and manually generating the ID would be one easy solution. As the database is purged of unneeded records a scheduled configurable process would run to determine when to "reset" the counter.



Woody Z
http://www.learntoprogramnow.com
 
Old January 19th, 2007, 01:59 PM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

I agree with Jeff in that if the poster is looking to stray away from large values as the PK then GUID's would not be the way to go however manipulating a counter table isnt overly difficult as woody said. All and all there is no "easy" way to accomodate the OP's request.

================================================== =========
I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.
================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
 
Old January 19th, 2007, 02:21 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

...but an Autonumber column is a counter...

When I spoke of performant solutions, I was not talking about the performance of the table per se, but rather I was thinking about what it would take to identify gaps in the numbers assigned and re-use them when rows are deleted. From the OPs description, I assume these deletions are essentially random, so there would be gaps all over the place. Finding them for each newly inserted row takes time.

I see no advantage to using a GUID in this situation; there is no need to use a GUID when an integer will do.

What we really need to know is what these numbers are actually doing that the OP thinks that 8 or 9 digits is "enormous" for a user to refer to. I note in another post of his that the "...table would still contain another million records or so ...". Given that, he'll need at least 7 digits to uniquely identify any of the million existing entries, so what's another couple digits??

I see that the OP still has not responded as asked with an explanation of what he is trying to accomplish. That would be helpful.

Jeff Mason
Custom Apps, Inc.
je.mason@comcast.net
 
Old January 19th, 2007, 03:37 PM
Friend of Wrox
 
Join Date: May 2006
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes. An autonumber is a counter, but not one that the programmer can control as the OP seems to think that he needs. If this is not accepatble due to records not being purged in some sort of first in - first out manner, then there are ways to find and make available unused ids.

For example, a process can be run that identifies the first 10,000 unused id numbers and places those into a table. When those are used up, the process runs again. Should be no performance problem with that.

Another scheme that is also very easy to use is a compound primary key that for the first column uses a counter that resets each day (using a counter table) and the yyyymmdd as the second column. This guarantees unique ids until the year 10,000 and then the y10k issue will raise its unseemly head.

Both the arbitrary constraint of "reusing" numbers and the arbitrary constraint of "no huge numbers" are red herrings, in my opinion. The whole problem is really arbitrary except for the apparent fear of the OP of running out of numbers.

The purpose of using a guid is to fulfill the users requirement:
Quote:
quote: is there a way round this so that each record continues to have a unique identifier generated automatically regardles of the number of times the records are inserted and deleted?
It seems the GUID solution is the easiest (if "easiest to implement" is a requirement) as long as there are no aritficial constraints for the size of the id. I just tested using an Autonumber field with the datatype set to ReplicationId and inserted 10,000 records. No problems. Then I inserted 1,000,000 records. No problem. Deleted and reinserted arbitrary sets of 10,000 records - again, no problem.

Of course, discussing this is without purpose if the OP is no longer interesed.

Woody Z
http://www.learntoprogramnow.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto Number query again Brendan Bartley Access 4 August 26th, 2006 06:09 AM
problem with Auto Number Brendan Bartley Access 6 August 14th, 2006 10:13 AM
[b]Auto Number[/b] vanjamier Classic ASP Databases 1 November 18th, 2004 03:44 AM
Auto number yami56 Access 1 August 20th, 2004 11:24 AM
Auto number Sheri Dreamweaver (all versions) 0 December 22nd, 2003 03:36 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.