CODEWITHSUNDEEP
mysqlauto-increment
Daniel Nordh
Daniel Nordh

Reputation: 390

Autoincrement non-unique column

I am trying to create a queue system for pre-orders for our webshop. Sometimes we have more orders than stock for a few deliveries to our warehouse and I'm trying to organize so that the people that made the orders first gets their products first.

The problem comes when a customer wants to make a change to an order, by for example adding something to it. For booking purposes we then make a return of the first order to our system which creates another order, and then finally create a new order with everything on it. This causes this customer to be last in the queue list in our current system where we go by date created.

What I would like to do is to have the original queue spot be copied over to the new order without messing up the autoincrementing. This also means that there will be three orders (Original, plus return, plus new) with the same number.

id  | order   | queue | ordercomment
1   | 1001    | 1     | new order
2   | 1002    | 2     | new order
3   | 1003    | 3     | new order
4   | 1004    | 1     | return order 1001
5   | 1005    | 1     | corrected order 1001
6   | 1006    | 4     | new order

Is there any way to handle this without making a manual incrementing solution that checks for the current highest number whenever an order is made?

Upvotes: 0

Views: 54

Answers (1)

David
David

Reputation: 219037

where we go by date created

But your data has no such date. You're relying on incrementing integers to determine the sort order, and that's where you're running into trouble.

If you want to sort by the date created, store the date the order was created. Any time you modify, append, or otherwise recreate an order you can still preserve the original order date. Perhaps with two columns, the date of the current order and the date of the original order. (For most orders these two values would be the same, but there's nothing wrong with that.)

Then your order of priority would simply be the date of the original order.

Basically, don't try to use an integer as a timestamp. Use a timestamp.

Upvotes: 0

Related Questions