This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Performance and the fly likes Finding an optimal solution Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Java » Performance
Bookmark "Finding an optimal solution" Watch "Finding an optimal solution" New topic

Finding an optimal solution

bilal haider
Ranch Hand

Joined: Feb 08, 2011
Posts: 47

I am having tough time in my application.I have very short time implement the functionality. This is scenario. I need to insert/update a record in a remote site. That

site is having different look up values than the local site. Look up values are dependent on other look up values. Central db have different values of look up than local

db. Now when i insert/ update a record of local db in central db vice verse, i get integrity constraint error because i have different values of look up. e.g

Local Database 1:

Designation Table
ID Name
1 Researcher
2 Programmer
3 Office Boy

Employee Table

ID Name Designation_ID(FK)
1 John 1
2 Alex 2

Remote Databse

Designation Table
ID Name
10 Manager
11 Dy Manager
12 Programmer
13 Researcher
14 Office Boy

Employee Table

ID Name Designation_ID(FK)
100 Tim Lyn 10
101 Bruce 12

Now i want to insert the records of local db in central db. and central db in local db. I can do the following.

Solution 1:
Create the lookup of all the local dbs inside central db, (overhead/redunduncy)
Solution 2:
Send the Designation names list with the records and match the strings at both the sites and insert/update repective ids. i.e John will get the designation id 13 when

inserted in central database and Bruce will get designation id 2 when inserted in local database.
In this case every time i am sending a record for insert / update i am sending a complete list of the Designation Lookup table.

Local databases communicate over the webservice with the central database. Please suggest. I am not in a position to pick the solution? Any Other Solution?

Emanuel Kadziela
Ranch Hand

Joined: Mar 24, 2005
Posts: 186
How about structuring your queries as follows:

let's say I want to add a new office boy named Joe to all databases -
bilal haider
Ranch Hand

Joined: Feb 08, 2011
Posts: 47

very cool. so simple.
Consider Paul's rocket mass heater.
subject: Finding an optimal solution
Similar Threads
database access pattern
problem while inserting the record in db using mySQL
Issue in inserting into DB using Hibernate, Spring
how to create insert, update, delete buttons on the same page?
Concurrency issue for dimensional tables