I have 2 databases (1 MSSQL, 1 Oracle) that I would like to synchronise some data between.
The MSSQL database contains the following fields:
number,FirstName, LastName, Permissions
With 'number' being unique.
The Oracle database contains:
id,FirstNames, LastName, Email
with both 'id' and 'email' being unique.
The MSSQL database can contain the same person over a number of different rows. eg.
79 Joe Bloggs General
85 Joe Bloggs Special
Both database are regularly updated.
## Deliverables
I have 2 databases (1 MSSQL, 1 Oracle) that I would like to synchronise some data between.
The MSSQL database contains the following fields:
number,FirstName, LastName, Permissions
With 'number' being unique.
The Oracle database contains:
id,FirstNames, LastName, Email
with both 'id' and 'email' being unique.
The MSSQL database can contain the same person over a number of different rows. eg.
79 Joe Bloggs General
85 Joe Bloggs Special
Both database are regularly updated.
It is important to note that the MSSQL database does not store historical information. For example if Joe Bloggs is updated his entries may change to:
99 Joe Bloggs General, General2, General3
85 Joe Bloggs Special
I need to capture this historical information. This may require a third database. The third database would either need to be Oracle or MySQL.
I have a small script that can pull data from the MSSQL database and attempt to match items with the Oracle database that can be provided. Note that probably less than half of the entries in the MSSQL database have corresponding oracle entries.
I need to match up the databases based on firstname/lastname and run a script that regularly synchronises them.
I need a GUI interface for 'questionnable' matches, eg. if there is more than one 'Joe Bloggs' in the oracle database so that a human can intervene and make the decision on whether or not it's a match.
Matches must be stored as the script that runs will continually harvest data.
The data, once stored must be searchable on 'num', 'id', 'firstname' or 'lastname'. Historical results should be viewable. I can create the search interface myself. This is just a note so that the data is stored in a manner that's easy to search.
The script will run regularly (eg. once per day) to create new matches/store new information. The script should have an interface that stores the 'questionnables' until a human decides to view it through a web browser and make a decision on the potential matches.