Hello, I am trying to switch from MS Access to a more reliable Java FrontEnd and MySQL backend version. I have managed to design and display records from the table, link tables and display records on the same interface.. Now I am stuck up at UPDATE. Problem is because my Customer table is big.. like 88 columns.. I know how to update table.. but want a better efficient way to do it..
I am thinking of few options,
1. To update the entire row if the button is pressed.. (which is a lot, as sometime they might only change the Title from Ms to Mrs).
2. Create flags, and update columns only if the flag value is true.. (this is highly expensive, as it needs 88 flags declared, 88 if conditions for computing which fields to update)
3. Create tabbed panels, and update only the panels where changes are made. (this is again going to be huge, as we might end up in condition 1, if only one field is changed in the particular tab)
If the above options are all rubbish, can someone give me a better idea for update?? I am worried a bit about this..
By far the most common, and the simplest, way to do this would be to have just one UPDATE query, and variables to hold values for all 88 columns, originally populated from the database. When you show the GUI, you set the initial values of all of the GUI widgets from those variables; when the user presses OK, you set the values of the all variables from the GUI (although many of them won't actually change.) Then you use that one query, setting all 88 column values in the query from the variables. Most of the columns being updated won't actually change, but that's really OK; a good database is going to optimize those extra column changes away anyway.
Don't worry quite so much about little efficiencies. 88 columns might seem like a lot for a person to run through, but it's nothing for a computer; introducing complicated logical to try to update only changed columns would take a lot of your time, and lead to complicated, hard to maintain code, but save little if any computing resources.
In a real system, by the way, those 88 variables will commonly be member variables of a class; an instance of that class represents one row of the table.
Hello Ernest, Thanks for the quick response.. I did feel it was okay to do that.. but just wanted someone to backup what I felt right was actually right.. Reading your comment was encouraging. I am working on that, I will keep this thread open for a while, if someone is giving another opinion.. But thank you so much for the response.. Much appreciated.