Criteria to Use Component Interface

I recently was screened by a potentially new client and was asked the following:

When building an interface how do you decide when to use Component Interface and when to use direct sql?

I thought my answer may be helpful to some people.

The criteria to use Component Interface versus straight SQL is actually pretty straight forward and CI wins about 99% of the time. If there is an existing data entry component that a user keys data through using the web browser, then CI is probably the way to approach the integration. Additionally, if the records/tables are delivered core tables like “person” data, you must use CI because the underlying code is constantly changing with patches, new enhancements and customizations.

If you use CI, the client application invoking the CI API generally does not have to change or even know the underlying code in the component changed as long as the data structure did not change. A data structure change would cause the structure of the CI API to change. This is the power of CI.

The straight SQL approach is generally a really bad idea because you have to recreate complex business logic in another application or module which is impossible to keep in sync in a real business environment. In Software development, duplicate code should be avoided at all costs. Additionally, the amount of code required to “create a person” using CI versus SQL is orders of magnitude smaller with CI for an experienced developer. Less code means fewer chances for bugs and less support headaches.

The question often comes up “When is it not appropriate to use CI?” In my experience, there are a few places where I have seen CI used where it did not make sense:

• Inserting into a very simple run control table prior to scheduling a process via PeopleCode. This does not really hurt anything but generally run control pages have very little business logic and rarely change.
• An architecture where you have an “interface table” whose sole purpose is for some third party to dump data into then some other batch process knows how to read this interface table. In this case, creating a CI for the interface table would not gain you anything as the interface table is meant to do mass inserts into via SQL.
• CI can be slow so if you have some really tight processing timeline like a weekend conversion go-live window to convert a massive amount of data then CI can put the go-live window in jeopardy. SQL is orders of magnitude faster however the level of research and testing required also has to be considered. Let’s say you have a weekend data conversion of 100,000 People from some external system. The processing time of CI could cause problems. If the timeline cannot be expanded for the use of CI, then you have to weigh the pros and cons of not using CI and also do some performance testing. In my opinion there are too many issues with doing straight inserts into core database tables for either a one-time conversion or an onboarding interface. I have personally designed conversion programs to run with concurrent processing to get around the tight go-live windows and slow processing times but hardware at different layers of the application can be a limiting factor.