I am working on a project where I have to sync data/contacts present in our external database and Constant Contact. I have questions about organization of data in the database and management of email lists in Constant Contact. I use SQL server database. I am aware of the API and the various methods it offers to sync, I even built a POC and the syncing worked for a simple and a sample list. The problem comes when I have to sync all the actual lists present in Constant Contact to the data in our database as the data is structured differently, i.e., there is no separate table for each email list. The contacts are organized in a single master table and assigned various classifications. The email lists in Constant Contact were created/grouped manually (there is no rule as such for which contact in database should go into which email list). I am wondering if I should create more tables in my DB, one for each email list and sync each table with a list? If I do this then won't I end up doing double syncing (1. Master Table and Sub tables created based on email lists,2. Sub tables and email lists in Constant Contact)? I cannot get rid of the master table at any cost but can modify them to add any flags or map list ids. This means I will need to build a GUI in the native app to assign contacts to lists, etc. The aim of this project is to reduce the manual work of managing email lists and keep the contacts up to date in both locations.
I am looking for any effective methods/ideas/solutions, best practices to achieve this. If you have faced similar challenge while syncing your data could you please explain how you achieved this?
Our other customers are of course welcome to comment here, but I did have a few questions regarding your database usage. If you would be willing to email us at firstname.lastname@example.org I would like to get some details that may be better off not being on a public forum.