Finding an optimal solution

[bilal haider]

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]

How about structuring your queries as follows:

let's say I want to add a new office boy named Joe to all databases -
insert into EmployeeTable(Name,Designation_ID) select 'Joe',ID from DesignationTable where Name = 'Office Boy';

[bilal haider]

very cool. so simple.