Board index » delphi » large numbered primary keys

Tim Chemaly

Delphi Developer
Tue, 26 Apr 2005 19:09:52 GMT

large numbered primary keys

Hi
I have a table with a primary that is of typ INT. However each month a large
number of transactions are processed. I'm thinking in terms of possible
billions! For instance Mobile Network Operators send a few billion SMS each
month! How can one prevent the primary key from over clocking back to zero?
Would it be best to have a primary key that is a combination of the
YEAR,MONTH and some sequence number (SEQ_ID)?
In this case the next SEQ_ID is obtained using...
SELECT MAX(SEQ_ID)+1 FROM MY_TABLE WHERE (THE_YEAR=2002 AND THE_MONTH=10);

How else could one overcome this problem?

Bye
Tim

--

Regards

---------------------------------------------------------
Tim Chemaly
Internet Application Developer

Solarworks
website:  www.solarworks.co.za
email:      t...@solarworks.co.za
mobile:    +27 (0) 82 729 0667

 
Arno Brinkma

Delphi Developer
Tue, 26 Apr 2005 20:27:20 GMT

Re:large numbered primary keys


Hi,

Quote
> I have a table with a primary that is of typ INT. However each month a large
> number of transactions are processed. I'm thinking in terms of possible
> billions! For instance Mobile Network Operators send a few billion SMS each
> month! How can one prevent the primary key from over clocking back to zero?
> Would it be best to have a primary key that is a combination of the
> YEAR,MONTH and some sequence number (SEQ_ID)?
> In this case the next SEQ_ID is obtained using...
> SELECT MAX(SEQ_ID)+1 FROM MY_TABLE WHERE (THE_YEAR=2002 AND THE_MONTH=10);

This is not safe in a multi-user system !

Quote
> How else could one overcome this problem?

Definitly use an generator.
Create for every year a generator and with that generator you can use up to 2147483647 (INT) postive
numbers (and if you also allow negative then 4294967295 numbers) is this enough ? Or create a
generator every month, but i don't know the exact number of maximum generators you can create.
Maybe someone else who knows ?

Regards,
Arno

Martijn Tonie

Delphi Developer
Tue, 26 Apr 2005 20:44:04 GMT

Re:large numbered primary keys


Hi,

If you use a dialect 3 database (you didn't mention your IB
version), generators
are 64bit - if you use a NUMERIC(18, 0) as the datatype,
your values will be
stored as 64bit as well...

I guess you're not going to overclock that one :)

--

With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase
http://www.upscene.com

Firebird Workbench - the developer tool for Firebird
http://www.upscene.com

Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

Quote
> > I have a table with a primary that is of typ INT.

However each month a large
Quote
> > number of transactions are processed. I'm thinking in
terms of possible
> > billions! For instance Mobile Network Operators send a

few billion SMS each
Quote
> > month! How can one prevent the primary key from over

clocking back to zero?
Quote
> > Would it be best to have a primary key that is a
combination of the
> > YEAR,MONTH and some sequence number (SEQ_ID)?
> > In this case the next SEQ_ID is obtained using...
> > SELECT MAX(SEQ_ID)+1 FROM MY_TABLE WHERE (THE_YEAR=2002
AND THE_MONTH=10);

> This is not safe in a multi-user system !

> > How else could one overcome this problem?

> Definitly use an generator.
> Create for every year a generator and with that generator

you can use up to 2147483647 (INT) postive
Quote
> numbers (and if you also allow negative then 4294967295

numbers) is this enough ? Or create a
Quote
> generator every month, but i don't know the exact number

of maximum generators you can create.
Quote
> Maybe someone else who knows ?

> Regards,
> Arno

Tim Chemal

Delphi Developer
Wed, 27 Apr 2005 00:29:04 GMT

Re:large numbered primary keys


I'm using IB6.

--

Regards

---------------------------------------------------------
Tim Chemaly
Internet Application Developer

Solarworks
website:  www.solarworks.co.za
email:      t...@solarworks.co.za
mobile:    +27 (0) 82 729 0667

Quote
"Martijn Tonies" <m.tonies@upscene_remove.com> wrote in message

news:3dcbb123@newsgroups.borland.com...
Quote
> Hi,

> If you use a dialect 3 database (you didn't mention your IB
> version), generators
> are 64bit - if you use a NUMERIC(18, 0) as the datatype,
> your values will be
> stored as 64bit as well...

> I guess you're not going to overclock that one :)

> --

> With regards,

> Martijn Tonies
> InterBase Workbench - the developer tool for InterBase
> http://www.upscene.com

> Firebird Workbench - the developer tool for Firebird
> http://www.upscene.com

> Upscene Productions
> http://www.upscene.com

> "This is an object-oriented system.
> If we change anything, the users object."

> > > I have a table with a primary that is of typ INT.
> However each month a large
> > > number of transactions are processed. I'm thinking in
> terms of possible
> > > billions! For instance Mobile Network Operators send a
> few billion SMS each
> > > month! How can one prevent the primary key from over
> clocking back to zero?
> > > Would it be best to have a primary key that is a
> combination of the
> > > YEAR,MONTH and some sequence number (SEQ_ID)?
> > > In this case the next SEQ_ID is obtained using...
> > > SELECT MAX(SEQ_ID)+1 FROM MY_TABLE WHERE (THE_YEAR=2002
> AND THE_MONTH=10);

> > This is not safe in a multi-user system !

> > > How else could one overcome this problem?

> > Definitly use an generator.
> > Create for every year a generator and with that generator
> you can use up to 2147483647 (INT) postive
> > numbers (and if you also allow negative then 4294967295
> numbers) is this enough ? Or create a
> > generator every month, but i don't know the exact number
> of maximum generators you can create.
> > Maybe someone else who knows ?

> > Regards,
> > Arno

Tim Chemal

Delphi Developer
Wed, 27 Apr 2005 00:26:22 GMT

Re:large numbered primary keys


Quote
> Create for every year a generator and with that generator you can use up

to 2147483647 (INT) postive
Quote
> numbers (and if you also allow negative then 4294967295 numbers) is this

enough ? Or create a
Quote
> generator every month, but i don't know the exact number of maximum

generators you can create.
Quote
> Maybe someone else who knows ?

Hmm you would have to create a new generator every month into the
future...quite a lot of generators. You can delete old generators???

Bye
Tim

Tim Chemal

Delphi Developer
Wed, 27 Apr 2005 00:39:14 GMT

Re:large numbered primary keys


Quote
> Create for every year a generator and with that generator you can use up

to 2147483647 (INT) postive
Quote
> numbers (and if you also allow negative then 4294967295 numbers) is this

enough ? Or create a
Quote
> generator every month, but i don't know the exact number of maximum

generators you can create.

If I create a new generator won't it also start at 1! If so then I will have
two primary keys with the same value!

Martijn Tonie

Delphi Developer
Wed, 27 Apr 2005 00:45:02 GMT

Re:large numbered primary keys


"Tim Chemaly" < .co.za> wrote in message
news:3dcbe5c0@newsgroups.borland.com...
Quote
> > Create for every year a generator and with that

generator you can use up
Quote
> to 2147483647 (INT) postive
> > numbers (and if you also allow negative then 4294967295
numbers) is this
> enough ? Or create a
> > generator every month, but i don't know the exact number
of maximum
> generators you can create.
> > Maybe someone else who knows ?

> Hmm you would have to create a new generator every month
into the
> future...quite a lot of generators. You can delete old

generators???

Not an option - there's a maximum number of generators.

Better read my other post about 64bit integers.

--

With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase
http://www.upscene.com

Firebird Workbench - the developer tool for Firebird
http://www.upscene.com

Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

Arno Brinkma

Delphi Developer
Wed, 27 Apr 2005 05:18:25 GMT

Re:large numbered primary keys


Hi,

Quote
> > Create for every year a generator and with that generator you can use up
> to 2147483647 (INT) postive
> > numbers (and if you also allow negative then 4294967295 numbers) is this
> enough ? Or create a
> > generator every month, but i don't know the exact number of maximum
> generators you can create.

> If I create a new generator won't it also start at 1! If so then I will
have
> two primary keys with the same value!

I meant a primary key with YEAR & generated id together, but take first a
look on NUMERIC(18,0) !

Regards,
Arno

Other Threads

1. Primary key or not primary key.........

2. WHAT'S THE LARGER SIZE STRING TO USE IN A PRIMARY KEY

3. Primary key corrupt with large filesizes (>375 MB)

4. Number out of range error on Pdox table create w/autoinc primary key

5. Pdox AuoInc as Primary Key in D4 gives Number out of Range error - Pls Help

6. Wrong numbers in primary index (auto-number, Access mdb) -2nd post

7. Wrong numbers in primary index (auto-number, Access mdb)

8. change Primary Key when duplicate key error during post

9. Primary Key vs Unique Key

10. Key violation for auto increment Primary keys