General C++/Database Programming Question

I am using SQL Server 6.5, Visual C++, and COM to tackle a personal project.  I understand the use of Primary Keys, Foreign Keys nad other referential integrity mechanisms when designing a database.  But, in the COM world, where the business logic is often sandwiched between the data layer and the application layer, are referential integrity mechanisms commonly used?
My direct point is that it seems redundant for me to implement foreign keys in a SQL database design only to code the knowledge of these table relationships into my business COM objects.  I've consulted various texts and can find no information that helps me understand this area.
I'm not looking for a two page explanation, I'm a fairly sharp guy, but in six sentences or less could someone point me in the right direction?
Thanks.
Neil KiserAsked:
Who is Participating?
 
Answers2000Connect With a Mentor Commented:
You should still use the referential integrity mechanism in the database for a number of reasons :-

1. Are you sure that the COM code will be the only code that will ever access the database ?  For example if you do dataloads or reports on the database directly ever, or even might do, then this would be a big advantage.

2. Are you sure that the COM code will be right first time ?  If you're not (how could you be ? <g>) then adding constraints (and checking return values in your functions) will help you debug your COM code (for example if breaks a uniqueness constraint).  I would especially recommend this if you are writing the COM code in C or C++, but regardless of language this applies.

3. Are you sure that the COM code can not be "mis-used" ?  Unless you define your interfaces perfectly, could somebody in 1 year or 5 years time develop an application that used the COM functions in an unexpected order or with unexpected parameters that messed up your database ?

4. Are you sure that the COM code can never fail part way through a "logical" transaction ?  If the COM code crashes or fails half way through an operation the database code be left in a undefined state (example: orphaned records).  Using DB Transactions/Roll-back can help, but doesn't 100% prevent problems of this type, as how you use the database (or write your COM code) may not exactly fit in all cases "logical" transactions to the transactions as implemented through the database API.

Bottom line: use database integrity mechanisms for additional security, doing so will also help you debug & verify the COM code as correct.
0
 
yonatCommented:
See http://www.kinetica.com/ootips/persistent-objects.html for:
* A two-page explanation of the problems involved
* A list of commercial products that provide solutions
* Many links that can help you tailor your own solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.