Win a copy of Learn Spring Security (video course) this week in the Spring forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Design question - update + insert versus delete + insert

 
Shweta Parekh
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have a design dilemma here.
I have a 2D array coming from my front end.
array[0][0] = "abc"
....
array[0][2] = "bcd"
....
array[5][0] = "xyz"
....
array[5][2] = "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.

Thanks,
Shweta
 
Purushoth Thambu
Ranch Hand
Posts: 425
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Shweta Parekh
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your response.
We are using Oracle DB.

-Shweta
 
Virag Saksena
Ranch Hand
Posts: 71
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And before updating, check if the rows have actually changed, rather than doing a needless update.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic