How to re-use id's in a database

[kittrellbj]

I am designing a strategy game, and one of the features is moving units from one place to another.  Since this will be a common activity in the game, I want to be able to re-use id's in the database that have been deleted.  For instance, unit ID=1 moves to unit ID=2 location, a battle occurs, unit ID=1 is destroyed.  I want the system to re-use ID=1 at some point so that the system won't hit (at least without a whole lot of movement going on at the same time) the maximum number of ID's in the database (BIGINT).

 

Now, I know that BIGINT is a super-huge number, but I'd rather plan for the future now than have to deal with it in the future.  Any thoughts?  (or alternatives)

[FaT3oYCG]

or you could add a feild for each unit in the table called alive and then query and update the database as units are killed / created and then you simply need to update the database to kill or revive a unit

[kittrellbj]

Well, I decided to do a few google searches about this topic, and I guess I will just leave it be.  A website I found (http://forums.mysql.com/read.php?10,97426,97519#msg-97519) states:

 

It's also a needless operation to try to make the primary key values contiguous because you're worrid about running out. If you use an ordinary datatype like UNSIGNED INT for your AUTO_INCREMENT column, you have 4294967295 values. You can insert 1 row per second, 24 hours per day, for 136 years before you run out of values.

 

If that's a problem, then use UNSIGNED BIGINT. That should handle 1000 new unique records per second, 24 hours per day, for 584 million years

 

So, unless they come out with an immortality pill, I may just let my great^789994420020222114 grandchild worry about that, assuming that my software is still in use. lol

 

I use a unsigned BIGINT for all id's in the database, btw.

[FaT3oYCG]

hence would it not have been easier to just google rather than asking and completly discarding a method

[Maq]

Why are you using an unsigned big int for an auto-incremented number?  You're just wasting space.  And you shouldn't be re-using the auto-incremented id.  Do something similar to what FaT3oYCG suggested.

[kittrellbj]

The auto-increment bigint is used so that the cap on the records will (not in any reasonable time frame) be reached.  What would you recommend?

[FaT3oYCG]

The auto-increment bigint is used so that the cap on the records will (not in any reasonable time frame) be reached.  What would you recommend?

 

:-\ are you being serious

 

Do something similar to what FaT3oYCG suggested.

[Maq]

The auto-increment bigint is used so that the cap on the records will (not in any reasonable time frame) be reached.  What would you recommend?

 

I don't even think 4,294,967,295 people have internet capabilites...  This is a ridiculous statement, it's going to waste space, if you don't have to use it, then don't.  There are better solutions than this but if you want to ignore out suggestions, then why are you asking for help?

[kittrellbj]

Ok..... not sure why everyone is being mean about this issue.

 

I didn't say that 4 billion people will play it, but with an auto-incrementing ID of units that are added/removed from the database on a constant basis, since that number will always go up, and is not dependent necessarily on how many people are playing (only the speed at which new records are added), it seemed logical that I would want to make it so the maximum number would be difficult/never to reach.

 

I can see reducing the account ID's down to a lower-level integer, that's obvious that we won't hit 4 billion accounts.  However, after a while, we could hit 4 billion "click, move unit" entries, or that many building construction entries, or other things that users do very regularly and generate large numbers of records. (considering that things can be cancelled, changed to a different building/location/etc., with each cancellation or change being made into a new record of depart, arrive, etc.)

 

I didn't say I was going to ignore your suggestions.  I simply requested the reason why bigint should never be used under any circumstances when it has been recommended as a good option for the above by others.  Trying to sort through conflicting statements, is all.  I wasn't meaning to offend anyone.

[Maq]

I didn't say I was going to ignore your suggestions.  I simply requested the reason why bigint should never be used under any circumstances when it has been recommended as a good option for the above by others.  Trying to sort through conflicting statements, is all.  I wasn't meaning to offend anyone.

 

Sorry if I'm mean, but that's the way I explain things.  We're saying you shouldn't use it for auto-increment, like the quote from the manual says, it will take 584 million years if you increment it every second.  BIGINT is used in many situations, for example, phone numbers.

 

Instead of reusing the ID for the destroyed units just have an extra field in your table called, "alive", (like what FaT3oYCG said), that's binary (1 & 0), and when it's destroyed update that field to a 0 so when you check to see which ones to re-use (destroyed) you can just say WHERE alive = 0; and that will grab all your destroyed units.  Hope that helps.

[kittrellbj]

I'm having a little bit of trouble visualizing the alive/dead variable without an ID to make the record unique, in case there are multiple units on one location, with the same owner, and both/several units are alive, as far as being able to look at what vehicles/troops are in the unit.

 

i.e.

 

Name : Tanks : Troops : Alive

Army 1 : 50 : 100 : 1

Army 2 : 50 : 100 : 1

Army 3 : 50 : 100 : 0

 

I suppose, after drawing it out, I could use the name of the unit combined with an alive value combined with a location value to draw out the record.  I wouldn't need ID numbers then, but it could get hairy if the armies are named the same thing and are at the same location.

 

But, I will have a system in place to combine armies that land on a place with an army already there, so maybe it won't cause a problem.  Anyone see any obvious problems with a setup like this, or recommend another way (or modified way)?

 

Trust me, I'm all for having less fields in the table and making it more efficient, and didn't mean to portray that in any of my previous posts.

[kittrellbj]

Ahh, I think I am on the same page now.  You're not saying to do away with the ID, just don't make it auto-increment, and make it so the records will overwrite old ones in the database.

 

i.e.

 

ID : Name : Tanks : Troops : Alive

1: Army 1 : 50 : 100 : 1

2: Army 2 : 50 : 100 : 1

3: Army 3 : 50 : 100 : 0

 

When someone deploys "Army 4", do a database check to get a list of records with Alive = 0 (destroyed), and update the database with the new information under that record ID, thus making the table:

 

ID : Name : Tanks : Troops : Alive

1: Army 1 : 50 : 100 : 1

2: Army 2 : 50 : 100 : 1

3: Army 4 : 150 : 100 : 1

 

That would also make it so that the table would normally have less than a couple thousand records, making it a nice and fast solution.

 

Thanks!

[fenway]

BIGINT is used in many situations, for example, phone numbers.

OMG, seriously?

[Maq]

BIGINT is used in many situations, for example, phone numbers.

OMG, seriously?

 

Lol, you're right they should be stored as VARCHARS.  I was in a rush writing that, and my point was that he shouldn't be using them for auto-incremented fields.

[fenway]

BIGINT should never be used for anything -- ever.