01-20-2010 01:15 PM
I am a developer hired by a client to redesign and migrate their database from a hundred ad hoc unrelated Access tables into an integrated SQL Server DB with likely follow-on work to convert applications and create new apps. The client has just made the decision to adopt ACT Premier with the SQL Server which will contain Contacts and Companies (and maybe Employees??) but the rest of their data needs must be handled outside of ACT. I have just finished the ACT white paper, "Architecture, Customization, and Integration," but remain unclear as to whether I will be able to implement some of what the client is looking for.
My key issue is that dozens of tables must relate directly to Customers, Companies, and Employees. I know how to implement this logical relationship between two separate SQL databases by causing them to behave as one logical DB. And I think, based on the white paper, that I can implement this OK but would appreciate some confirmation or hearing from anyone who has done this already.
My biggest concern is dozens of business transactions that the overall system must support require either validating the existance of an ACT-hosted record before creating a record in a related table (again, the white paper seems to indicate that this can be done) or creating a new record in Contacts or Companies (or both) and immediately retrieving the primary key value of the newly created record and using it as a foreign key in a related record outside of ACT.
My client's IT manager is reporting that ACT actively discourages external applications from directly writing to the ACT database but that some users do it. Are you one of those users? Can this be made to work?
Does ACT use identity attribute (autonumber) primary keys in its table structures for Contact and Companies? Will the ACT PKs ever change?
Can ACT be configured to raise an event of some sort if certain actions take place? That is, if a Contact record is deleted can I detect this to either prevent it if there are related records outside of ACT?
Sorry for the rambling message. If you are an experienced developer you will understand what I am writing about and my concerns.