April 13, 2009 at 1:26 pm
Many years ago, when I was an SQL newbie, I designed a database that I considered 'relational'. It was normalized as best as I could to 3NF. It had tables with primary keys and child tables with foreign keys. I did not, however, define those as FK relationships or constraints in the DB. All primary and foreign keys were indexed. RI was enforced through the program code, not the DB. I have been told that, since the relationships were not defined, that my database was not relational. Two questions:
1. Would you consider this DB to be a relational DB, albeit poorly designed?
2. Is there any performance gain (or loss) in defining FK relationships/constraints assuming all PKs/FKs are indexed? I realize there is a data consistency advantage, but I am asking about performance.
TIA
John
John Deupree
April 13, 2009 at 3:13 pm
Hi John
John Deupree (4/13/2009)
1. Would you consider this DB to be a relational DB, albeit poorly designed?
The database is relational but not of integrity 😉
2. Is there any performance gain (or loss) in defining FK relationships/constraints assuming all PKs/FKs are indexed? I realize there is a data consistency advantage, but I am asking about performance.
It costs a bit of performance because the FK entries will be validated by SQL Server to their parent tables. Anyway this price is much less than corrupt data.
Much more important thing you should consider before adding the FKs:
If your application was not enforced to handle those FKs you may run into problems depending on the applications data flow. If your application creates "Order" objects and "OrderPosition" objects in one step it doesn't matter since now if the "Order" or the "OrderPosition" objects was written as first. If you create the FKs and try to insert an "OrderPosition" since the "Order" is not yet available you will get an error. Maybe you have to swap some code blocks.
It is advisable to add the FKs but check if the application can handle it.
Hope this helps
Flo
April 13, 2009 at 3:23 pm
Thanks for the reply. It's funny you would mention the application code handling it. That's exactly why I didn't add them in the first place. I had no control over whether the child or parent would be added first.
John
John Deupree
April 14, 2009 at 7:43 am
John Deupree (4/13/2009)
1. Would you consider this DB to be a relational DB, albeit poorly designed?
It's relational. I don't know enough to say it was poorly designed, but eliminating FK's means it's VERY likely to lack data integrity, which would equate to a poor design.
2. Is there any performance gain (or loss) in defining FK relationships/constraints assuming all PKs/FKs are indexed? I realize there is a data consistency advantage, but I am asking about performance.
As Flo says, there is a performance hit for FK's. However, good indexing can, not only largely eliminate that, but make it into an advantage.
Either way, data integrity sounds like your primary issue. Since you don't have referential integrity, does the app handle rollbacks and failed transactions correctly or do you have orphaned records? Lots of orphaned records are going to lead to bad data in reports and business problems down the line. That's what bothers me about this design, not a strict adherence to a standard, but harm to the business that the database is intended to support.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 14, 2009 at 11:28 am
It realy depends on the architecture of the system on where to define the business rules. If it is an OLTP system i would like to define all the PK and FKs. I would like to take the hit on performance compared to maintaining the data intigrity.
If it is an OLAP system then i will create the FKs but in disable mode. This is just to understand how the tables are related.
April 14, 2009 at 1:15 pm
Thanks all for the replies.
It was an OLTP application. The DB was a bit unusual in that there were very few deletes. We (the DBAs) would find orphans rarely and deal with them. Reporting would have been unaffected, however, because reports were always 'top down' reports that included parent records. Orphans would have been excluded by inner joins in the report query. This was a small app with 1-2 developers at any one time, so it was easy to control. That being said, it's no excuse for taking shortcuts. The ones who lose in that scenario are the DBAs (me) who don't learn best practices (or take longer to discover them).
John
John Deupree
April 15, 2009 at 6:09 am
Some commercial products that are designed that way, for example Microsoft's Retail Management System. The application handles all of the integrity issues & business logic.
April 16, 2009 at 10:53 am
For that matter look at the SQL Server system databases.
Not so much anymore in 2005 and moving forward.
Check out master in a SQL 2000 or earlier version - it'll really open up your eyes !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply