0

I was so vague on this set-up that I've been searching and reading mysql documentation and can't found something that really explain my problem.

My problem is I have Two Table, table one is 'singlcustomer' then the second table is 'pickup_singlecustomer'.

'singlecustomer' table has an auto-increment id field.

singlecustomer table has this data, with id as auto-increment

|id| |name| |item |

|1 | |John| |Bike |

|2 | |Mike| |Car |

|3 | |Carl| |Scooter|

In pickup_singlecustomer it has Identitical Fields to avoid problems but the id is not incremented.

The operation I have is whenever the Item is pickup by the customer I want to transfer it to the pickup_singlecustomer table and delete the one in singlecustomer. I added a feature of 'Restore' back to singlecustomer but whenever I restore a data lower than the Auto-increment I get an error. Also if I disregard the first column which is idit returns the data but with the next increment number.

From This singlecustomer table

|id| |name| |item |

|1 | |John| |Bike |

|2 | |Mike| |Car |

|3 | |Carl| |Scooter|

Lets say I mark Mike as Pickup, and send the row of data to pickup_singlecustomer then **delete it in singlecustomer

it looks like this singlecustomer table

|id| |name| |item |

|1 | |John| |Bike |

|3 | |Carl| |Scooter|

in pickup_singlecustomer

|id| |name| |item |

|2 | |Mike| |Car |

If I Restore Mike back to singlecustomer

it looks now like this.

|id| |name| |item |

|1 | |John| |Bike |

|3 | |Carl| |Scooter|

|4 | |Mike| |Car |

Is it possible to return it with the same id?

I have read something that you can set a value lower than the id, but I forgot the keyword and can't look it up anymore due to running googlechrome in incognito.

Also is mysqldump capable to do this? The purpose of this restoration of data Is to have flexibility for the User if he accidentally mark a wrong item, so the user might able to undo it.

Hope you can help me guys thank you very much.

Akagami Shanks
  • 19
  • 6
  • 1
    Its a lot easier to do things like this by eg not having two tables, not deleting rows and having a single status column indicating whether the item has or has not been picked up by the customer. You're essentially storing the Boolean knowledge of whether an item has/hasn't been picked up by placing the item in a certain table or not. What if one day you have 20 Boolean things to keep track of - have 20 tables? Look in all of them? Store the same row over and over if multiple booleans are true at the same time? Stop the impending train wreck. Use a status column – Caius Jard Jul 07 '20 at 05:15
  • Thank you Caius, I was doing that By Adding a column in update which say "Pickup" but it looks so messy, so I want to put Item in pickup table, so It can be view in another datagrid. Is this method not possible at all? – Akagami Shanks Jul 07 '20 at 05:17
  • 1
    https://stackoverflow.com/a/1884420/2716623 – vasily.sib Jul 07 '20 at 05:18
  • 1
    Does [this](https://stackoverflow.com/a/21646690/9363973) answer your question? Please also be aware of the warning in the linked question, there is almost never a reason to do this. For your use case I'd honestly not recommend doing it the way you are, because you'll potentially have a lot of data duplication due to duplicate fields (name and item). Instead simply reference the `singlecustomer` table in your `pickup_singlecustomer` – MindSwipe Jul 07 '20 at 05:18
  • 1
    Anything is possible, it's just that this is a terrible idea. If you don't want your status column in a DataGrid then (ignoring the fact that your grid doesn't have to render it) you simply *don't select it*. Do `SELECT name, item FROM table WHERE status = 'picked up'` for your Picked Up Grid and `SELECT name, item FROM table WHERE status = 'not picked up'` for your Not Picked Up Grid. "I used separate tables with slightly different columns and moved my data around between them because I didn't want my `SELECT *` query to return that column in some cases" is no valid reason at all to do this – Caius Jard Jul 07 '20 at 05:20
  • I also though of that Caius, but the problem is when I repopulate the datagrid with new item I need to always run query concerning notpickedup resulting to slow performance, even with just 10row, if ever it has more row I think it will slow down even more. – Akagami Shanks Jul 07 '20 at 05:29
  • Thank you for that Article mindswipe, what is the "Keyword" I search to reference a table to another table? That's what I want. – Akagami Shanks Jul 07 '20 at 05:30
  • I think you should find out why your 10 row data query performance is slow. – FanoFN Jul 07 '20 at 05:38
  • Thank you guys. I will use Caius Jard Suggestion and Sanitize it more for fast Performance. – Akagami Shanks Jul 07 '20 at 05:44
  • Indexes indexes indexes. But learn how they work too. We don't just slap an index on and then it'll definitely make a query faster. For example If you have a million rows and half are status "notpickedup" and half are "pickedup" and your only index is on status then the db might ignore the index because the effort of using it to look up all the half million rows needed then retrieving all the rows by their ID is greater than just scanning the entire table for the 500,000 rows. If index has good selectivity and the where clause uses one or more of its columns from left to right it should help – Caius Jard Jul 07 '20 at 18:19
  • Also, don't select more data than you need - don't include a 200megabyte blob file data when all you need is the file name etc – Caius Jard Jul 07 '20 at 18:20
  • Hi Caius It finally Work, But I have face another Error, I cant search data, with already queried table. When I search DAta even Pickup item shows up. – Akagami Shanks Jul 08 '20 at 07:54

0 Answers0