Keeping Track of a database

[Laker64]

Ok, I'm creating a customer database.

Basically, when I created it, this is the way I did it.

$query="ID int(10)
NOT NULL
auto_increment
PRIMARY KEY,
Name varchar(50)";

Right, so I have a database that looks like this:

-----------
ID| Name |
-----------
1 | Name1 |
2 | Name2 |
3 | Name3 |
4 | Name4 |
5 | Name5 |
-----------

Let's say Name2 decides cancel his business. When I delete that row (through phpMyAdmin), I get this:

-----------
ID| Name |
-----------
1 | Name1 |
3 | Name3 |
4 | Name4 |
5 | Name5 |
-----------

However, that's not very helpful. What I really want is this:

-----------
ID| Name |
-----------
1 | Name1 |
2 | Name3 |
3 | Name4 |
4 | Name5 |
-----------

Now, did I screw up when I created my database this way? Or am I screwing up when I delete things? Any ideas on how to make the ID maintain it's order? Thanks.

[daed17]

using auto increment forces the order. I would turn off the auto increment and make the id field unique. then you can write code to resequence the order. Make sure you update corresponding tables that might be joined using the id field.

[Laker64]

So then, you're saying take off the auto-increment. Then I would just figure out the value of the last row in the table, and add one to it, and make that the ID whenever I add a new row?

Also, when I delete a row, have a script that deletes it then decrements every ID field past that one by one?