Tricky data manipulation in MySQL

Tracker

Hi,
i have the following dilemma:
client wants to have a simple database with three fields where they want to be able to change ID field by themselves.
Simple as it is :

ID text text
1 --- ---
2 --- ---
3 --- ---
4 --- ---

What confused me was a required abillity to add entries into the middle of DB, and that in the case a new entry is added (or an existing entry has its ID modified) in the middle, the subsequent rows would have their ID number increased :

primary table:

ID text text
1 --- ---
2 new new
3 --- ---
4 --- ---
5 --- ---

Right now i think of solving the problem with:
1. checking whether new entry's ID($entryID) number is lower than the biggest in the table.

if it is, create a temporary table, and fill it with "SELECT * into temporary_table WHERE id>= $entryID"

3.update $entryID with new data

delete all fields from primary_table with a greater ID than $entryID
increase all ID numbers in temporary_table by one and merge
it back with the primary_table

Kinda tricky for one operation. =\
Perhaps somebody here can suggest a more short/efficient way to do it?

theeper

My question is ....

Is the ID field autoincrement ? If so, and this is what creates your problem, why not add a different column for the autoincrement and not let the users worry about it. Then the ID field that they may change has no bearing on the integrity of the data.

tomhath

I'd go back to the client and ask them what their real requirement is. Why does the design have to do what you describe? There must be some underlying problem they are trying to solve.

They've given you design for something, but that's not a requirement (and it doesn't look like a very good way to implement whatever it is they want).

My guess is that they don't have much database experience and are trying to force an old flat file design on you.

Tracker

Thanks for the replies,

theeper:

Nope, i removed the auto increment of ID field from the very beginning - there's no need in data integrity as this table is not linked to others.

tomhath:

Client's idea is to make an list of special offers on their webpage, and they want to use ID numbers to arrange order of those offers in the list shown on page. When i offered them another options of for example adding a date field and sorting the list by it, they said "No, we want it done exactly as we've told you. Its a simple database with 3 fields, aren't you able do it?".

...I figured that such primitive admin interface suits people with such knowledge of databases just fine. =)

PHPdev

Well if the ID field is not the Primary Key then you could duplicate the id entry and work logic with out the temp table.

Sorry but it sounds like you have one of those clients that knows enough to cause problems but not enough to correct them. I think we all have had one like this at one time or another.

I know the client is stirct about the table layout, but another option is to have

ID text text orderby
1 ----- ----- 3
2 ----- ----- 1
3 ----- ----- 2

using the order by colum to organize their stuff... this does not eliminate the need to renumber everything after an new insert but it would eliminate rearranging the entire table...

Just my .02

Tracker

Ah yes, the additional pID primary key field solved the problem,
with ID key no longer being unique it's possible to shift numbers up with a simple query -
"UPDATE $table SET id = id + 1 WHERE id >= $entryId"
and vice versa. Thanks for the advice all =)