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?
there is no auto_increment problem – leave the numbers alone, they don’t mean anything, and if you try to renumber them, you will, and i say this with complete assurance, shoot yourself in the foot one day
as for moving stuff out of the basket table into another table, that’s also not necessary – just add another column that indicates whether the item was bought
if anything, you can periodically remove old rows from the table that weren’t bought
Thank you for your reply. I will keep the single table as you suggest.
Would you simply add a word, say “yes”, after the user clicks to enter their card details? I was just thinking if someone clicks to enter their card details and it is declined or perhaps they change their mind and do not even enter their card details. Can you suggest the best way.
As you probably know, the customers BILLING address is entered with their CARD DETAILS on a secure web page. I will be designing the table to retrieve the BILLING address from the card-processing company (to appear on their bill/invoice) because I will not have it in my MySQL table - I will only have the delivery address. Would it be advisable to add the word, say “yes” at this stage indicating the purchase was made?