• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

update/ or delete & insert, question of attitude

 
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi guys,
I have a question of attitude:
I have a list of empl (group, name, address) and they all belong to a group (eg Accounting).

GUI: the user sees on the screen a list of all empl belong to accounting (there are ~ 50) now he wants to click update (cause for some of them he made some changes in address)

DB: Should he do this:
(1) *delete* all accounting group AND *insert* all NEW empl to NEW accounting
OR
(2) check if the group exist � if yes � *update*, no � insert.

It will be logic to perform number 2 but I just wonder why? Is it expensive to do insert? Is it bad programming?

Thanks for any pointers.
 
author
Posts: 4223
33
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, it is expensive and it is bad programming to #1 although surprisingly I have seen #1 actually done in practice. It's usually discovered when long numeric have reached their overflow limit (which in most systems should be impossible) or when performance grinds to a halt (due to constant record locking). And depending on the size of the table and the number of indexes it could be extremely expensive.

While it may be far easier to implement #1, since its a lot closer to saving/loading a data file, its not maintainable in the long term from a debugging perspective (how do you debug a problem with record 491 if record 491 was deleted?), nor does is even allow concurrency (what if one user was reading data while the other was erasing all the records).

Again, if your system has data access patterns similar to that of a file system, then it doesn't make sense to even use a database other than to enable users to find lots of bugs when they try to open more than one user at once.
 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Scott thanks for the quick educated reply.
I'll implement number 2.
 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
mmmm...here's something to consider:

1. When user deletes an empl from the list
2. When user adds an empl to the list in the middle (and order is important)

I am using an arraylist to store all empls (location is important)
so it looks like this:

1. update: update groupX set name=?, title=?... where groupName=? and ID=id
2. loop thru arraylist
3. get empl
4. executeUpdate

1. what happens if the arraylist doesnt contain an empl (the user removed him) how will I perform delete?

2. what happens if the user adds another empl to the list in the middle - for this I'll have to add anohter field - index.

any idea?
 
Scott Selikoff
author
Posts: 4223
33
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Peter,

What you're referring to is trying to precisely store a java object in a database. The thing is, there's no 1-to-1 mapping. Generally in a database you never have all of the data (imagine constantly keeping track of millions of records in a single array), therefore the comparison you gave isn't really applicable to databases.

You did mention inserting at random parts of the list which in a way relates primary key generation. Most times, a database gives a primary key (think of it as your array index in this situation) for every new record created. The thing about databases though, is that the condition is usually such that the key be unique, order is not important. So you may have records 1-5 (6 was deleted), 7-10, 194, 3958, etc. The common situation, though, is to use ever increasing keys so that higher numbers imply (but never guarentee) at least that many records have been by the system over the lifetime.

And yes, users can try to insert two records with the same primary key (or array index in your case). That's a non-trivial scenario with a number of solutions. The simplist is you let the database enforce the constraint (the db will never allow two records with the same key so one user will fail and the other will suceed). Another solution is to manage the primary key on the application server and have a static synchronized object or service that gives out only one key at a time.

Good primary key management can be hard especially in gigantic systems. Often times, recovery is the key (if one user fails and one succeeds,,, well then lets try the user that failed 2-3 more times) but then you have starvation and livelock issues best left to the performance forum...
 
There are 10 kinds of people in this world. Those that understand binary get this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic