• 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Finding an optimal solution

 
Ranch Hand
Posts: 47
Windows XP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?



 
Ranch Hand
Posts: 187
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 47
Windows XP Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
very cool. so simple.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic