Our Codeigniter application uses PostGreSQL as the primary database
And is also connected to a second database using Pervasive SQL via ODBC.
1. For both databases, we need get data from various tables and update each other
As an example, we have a customer table in Pervasive that needs to update the same Customer Table in PostGres.
If Customer doesn't exist in PostGres, need to insert new record or if customer does exist then update existing record.
Second example is we have sales tables in PostGres that need to insert into sales table in Pervasive.
Pervasive database is accessible via normal sql insert, update, delete commands.
The table structure between both databases is different so need to do some translation. As an example, FirstName is PostGres is called Name in Pervasive.
For the various tables in each database and translations between each database, we will assist you in understanding the differences.
Question --> How do your propose handling the translations / inserts / updates between the databases ?
2. The database communication needs to happen on a scheduled basis such as
every 10 minutes.
Question --> What method would you like to use for recurring updates ?
3. If update isn't successful for any reason, needs to handle this without duplicating or missing any data next update.
Question --> How would you like to accomplish update errors ?
4. Detailed logging either in database or log file to see history of each update such as date, time, table field, old value, new value, success, error, etc.
Question --> How would you like to do the logging ?
5. After each scheduled update, would like an email sent with Success / Failure along with summary of the operation.
Question --> How would you like to send notifications ?
In your response, the more detail you provide on each question the better.