Abrar Ahmad_ (7/5/2010)
Do Foreign key constraint implementation is fatal in terms of performance
FKs certainly add overhead but I would refrain myself of saying that FK implementation is fatal in terms of performance.
Having said that I have seen critical, high end systems - 60K+ executions per second - where each millisecond counted then no PK/FK was defined. In this case PKs got replaced by unique indexes and RI was enforced at the app side.
Abrar Ahmad_ (7/5/2010)
My design strategy for a medium level transactional DB (10 K - 500 K data records/Day) would be to not implement foreign key constraints?
There is a big difference in between 10K and 500K inserts nevertheless I agree with Gail this is not a large volume and provided hardware is appropriate I would enforce referential integritiy on the backend e.g. define PKs/FKs as business rules ask for.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.