I have a design dilemma here. I have a 2D array coming from my front end. array = "abc" .... array = "bcd" .... array = "xyz" .... array = "xxx"
My database table workSpec structure looks like this (there is no limit on the number of rows and columns) item Row Col Value 1230 0 abc 1230 1 abc 1230 2 bcd 1231 0 das 1231 1 das 1231 2 ada 123........ 1235 0 xyz 1235 1 www 1235 2 xxx
I run a executeUpdate query to update this table with 6 rows and 3 columns.
Now if the user on the front end adds rows and columns to the array ie. the array size is now 10 X 12 (10 rows, 12 columns). My question is in this situation would it be a better design to - 1. delete the existing rows from the table and do inserts for all 10 X 12 values 2. do update for 6 X 3 grid and do inserts for the remaining 4 rows and remaining 8 columns.
Any ideas? Any help on this is highly appreciated.
What database are you using? It's not good idea to delete the rows and insert again. The better approach will be to update existing rows and insert new rows as this will be performant when compared to delete+insert.