Auto-Increment Problem - And two tables for customer purchase info. query
I have a MySQL table that stores what customers have added to their basket. Obviously the number of entries will increase and I will need to delete some. ie. i do not need to store what a customer added to their basket 6 months ago (particularly if they never actually bought what they added to the cart).
My problem is when I delete them the auto-incrementing ID continues from the highest number. If I delete 1-500 of a thousand entries, when someone new adds something they are given the ID 1001. Eventually I will have a table starting at something like 14,000 and ending at 14,300, etc. which seems a bit daft.
I intend to have a second table that stores customer purchases. The table I refer to in the above 2 paragraphs will be stuff added to the basket. When the customer actually clicks BUY AND PAYS for the items in their basket I intend to add the details to another table and keep this information forever (or at least 6 years as required for the tax man).
Perhaps you know a way to delete an entry from the first table when it is added to another table (at the point of purchase) and do you know a way of eliminating my Auto-Increment ID problem in the first table when information is transferred to the second table?