Generating unique incremental integer ID for database tables

  • Thread starter Thread starter Claire
  • Start date Start date
C

Claire

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
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
Click to expand...

Look into UUID
 
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
 
How should I generate my "autoincremental" unique RecIDs please?
Look into UUID
Click to expand...

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
 
(inline comments)
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)
Click to expand...

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.
Click to expand...

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?
Click to expand...

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.
 
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
Click to expand...

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.
 
Back
Top