Subscribe to:
Post Comments (Atom)
skip to main |
skip to sidebar
The aim of this Blog is to provide a forum for raising questions and discussing the paper on Foreign Keys.
Download the Discussion Paper from here.
http://www.eternal-donut.com/downloadp/foreign_key_discussion_kratochvil_v11.pdf
The only rule that I ask is that you read the whole paper (in its entirety) before making any comments.
Eternal Donut Blog
Blog Archive
About this Paper
- Marcelle Kratochvil
- Canberra, ACT, Australia
- Marcelle Kratochvil is CTO for Piction and is the Oracle ACE Director for all data/any data. She has extensive experience in the business of setting up and manging multimedia sites, having setup over 30 in the last 5 years. Marcelle has been using the Oracle database for the past twenty years and originally began using Oracle with V5 on MVS and VMS. She has been awared PL/SQL Developer of the year in 2004, and has won awards for development of a shipping system and state government digital asset management. Over the last ten years Marcelle has been involved in the core development of Piction and has been involved in the installation and management of this product in countries around the world. She has extensive experience in Oracle Database Administration, including the performance of large databases, network management and application development. She has been a regular presenter at OpenWorld and first presented at OpenWorld in 1993. She recently presented at OpenWorld 2010 and has done internal presentation to Oracle. Marcelle is also an 11g Beta Tester and does tuning trouble shooting for Government Departments and commercial organisations.
Posts
Honestly, I DID try to read the whole paper. But it is very long and a lot of it is arguing about people closing their minds to new ideas, rather than explaining the new ideas. I CAN keep an open mind, but I also know VERY well the reasoning behind the old ideas, so you better do a DARNED good job telling me why it is wrong or why it doesn't matter anymore.
ReplyDeleteI'm going to pick out your Discussion point 10 - top 10 reasons for not using foreign key constraints, and answer each one of these in my own blog.
Hi,
ReplyDeleteI agree that there are some cases in wich not having a FK may be good, but to infeer that the relational model is broken is a bit far fetched.
I have seen some DML intensive DWs, where it was better not to use constraints. But that was only possible because the dimensions where (semi-)open or the closed ones would have a "No Information" entry.
The examples you gave did not disprove the relational model as well. In the multimedia example, if you're going to flag the child rows (with videos and photos), why not flag the parent row (collection) for delayed deletion as well?
Thanks,
Daniel Stolf
Relational is obsolete. Maybe a better word is dated.
ReplyDeleteRelational cannot properly deal with multimedia, xml, spatial datatypes as well as real world models and maintenance.
Just because I says its obsolete doesn't mean components used by the model aren't still valid. Remember that Oracle is object/relational.
This paper is not a debate on Foreign Keys. Its not that you are either for foreign keys or against them. I have avoided the use of the word debate. A debate implies black/white thinking (I am right you are wrong) without much room for compromise. If I got into a debate the end result wil be a polarisation of views without the real message of the paper being understood. Readers will debate the individual points and make arguments that in this case or this scenario the use is valid. That's not what this paper is about. I want to show that there are new types, new technology now available that did not exist when the Relational model was formulated. There are new rules now that the relational model could not anticipate, and that's why its obsolete.
As I point out in the paper, a new model is needed to deal with object/relational, multimedia and a whole new set of datatypes.
Relational deals only with the logical model. To use a foreign key just for performance reasons isn't using it in the true relational sense. What is happening is the original model is being modified to handle this concept.
I have picked on foreign keys deliberately. By showing that components of them are potentially flawed, and by showing that they are not the perfect solution to solving integrity and performance, then I have been hoping to highlight that the relational model is dated and needs changing.
"remember that the rules have changed".
ReplyDeleteBecause Oracle, "has" object components available, doesn't mean that every database built WITHIN that RDBMS is O/R. The reason they haven't been embraced has nothing to do with wanting to stick with the "tried and true" but because objects were far less flexible than the equivalent tables would be.
Your logic, in more than one place, relies on pointing out that the original intent was different than today's justification.
ReplyDeleteThat's not logical.
The original intent of Thalidomide was intended for morning sickness in pregnant women, it's now being used for treatment of Erythema Nodosum Leprosum and Multiple Myeloma. The original intent doesn't invalidate today's use. Words are repurposed, laws are repurposed, technology is repurposed. It's the nature of everything.
If FKs weren't designed to improve performance, but can now, who cares - We have jobs to do. If there's something better in SQL3, that's fanflippintastic, but I don't own an RDBMS that supports it.
>> Because Oracle, "has" object components available
ReplyDeleteThat is true. Its feasible to create applications that conform only to the relational model and they will work well.
Also note that Relational cannot deal properly with XML, Multimedia, Spatial and all the new types that are appearing.
By ignoring these types you potentially are limiting the scope and power of the application being developed.
Hence my analogy with analogue and digital phones.
Let me try another analogy. Lets equate propeller planes with Relational and jet planes with a better model (not necessarily object/relational, but for the purposes of this analogy this might have to do).
Propeller planes are still being used now, even used on commercial runs with passengers. They are useful on small legs and cost effective. Some might even argue they are safer than jets.
Jets planes can travel further, carry more passengers and have proven a better way of flying. Some would say propeller planes are dated, but they still have uses and in some cases do not need replacing.
But to ignore jet planes or ignore the new technology just because the two aren't compatible means that a whole new mode of transport is lost. And to use propeller planes when a jet would be better is bad business.
Now in the future there might be better methods than jet, so we need to always be aware of this and be willing to change.
And you are right, not everyone has to fly jet, but my perception of everyone's view in the marketplace is that they only see the way to fly is propeller. They are ignoring the jet. One reason I believe is that this takes them out of their comfort zone.
Go to an Oracle conference today, (official or user group) and look at the amount of topics and papers covering Database Objects as well as multimedia. Hardly any, if any. Only a couple at OpenWorld this year covering this out of hundreds of technology papers.
>>That's not logical.
ReplyDeleteYou probably were not around when the original relational debates on the purpose and intent behind NULL values was discussed in the 80's. It was obvious then that the relational logical model had an intent behind it that was not meant to change.
This debate forced a rethink on core relational concepts that are still not resolved today.
It was also clearly highlighted in the original relational model that it was for logical only, and not physical. The physical model (which is performance usage) was to be treated differently.
The relational model was logical only and to use it for physical has meant that its intent has changed and should be reviewed. And if you are willing to change its original intent and use it for something different means that maybe a whole lot of other relational concepts also need reviewing.
In your analogy, if you equate Thalidomide with Foreign Keys, then the original intent was to use them to enforce referential integrity.
As you correctly point out, Thalidomide is now being used for treating new illness. Its not being used anymore for its original purpose, in fact its dangerous to use it for its original purpose.
And I bet they are not even marketing it as Thalidomide because of the bad name it has. Its also likely been re-engineered and re-tested for its new role to treat this new illness.
Which is exactly what I am trying to say with Foreign Keys and performance. The feature which gives the better performance should be re-badged and given a correct name. It should be marketed correctly.
The performance feature can be used for non relational systems and its usage shouldn't be equated to the relational model.
You are right in saying words and laws are re-purposed, but some of the greatest problems we experience with laws are when the original purpose behind them (their original intent) is not stated.
If we did, more laws would be repealed because their original intent is now obsolete.
In HTML early days vendors came up with the 'embed' tag for displaying multimedia, flash and other non standard objects. That's not what the tag was originally for and it's use changed as HTML changed.
The standards committee made the tag obsolete and came up with the 'object' tag instead. So because the initial intent behind the 'embed' tag changed, rather than sticking with a dated concept, they re-invented it into a better, more formal structure called 'object'.
Marketing and rebranding. I agree with your own analysis. Marketing is for sheeple. People too oblivious to recognize the bill of goods they're being sold. The other 5% of us non-sheeple, work in logic and proof. Detroit has to prove to me their cars don't stick before I'll give up buying Hondas and Toyotas. I don't care if you rebadge them or not.
ReplyDeleteIt seems like this paper has cross-purposes. In some instances, you're looking for current RDBMS users to give up the usage (OK, the religious usage) of FKs and in other instances you're looking to change the way RDBMS's are engineered and package. One view is today and one is tomorrow.
I think you'd be better off seperating the two. One should discuss the future of relational models, the other should discuss the proper role of FKs today. Conflating the two makes a real debate harder.
"If we did, more laws would be repealed because their original intent is now obsolete."
ReplyDeleteWow, that's naive. ;-) Politicians don't make a name for themselves repealing laws about where horses can be watered in downtown Sydney. They seek to continuously add to their own power. Laws aren't repealed because their anachronisms aren't apparent but because there's no benefit to those with the ability to do so.
It's an interesting read - thought provoking.
ReplyDeleteLike most things, if you're an edge case then sometimes you need to think outside the box, forget about conventional wisdom, etc.
However, just because something is not suitable for your edge case, doesn't mean it's not suitable for 99% of situations.
So, in the multimedia world, fks don't work for you - fine, no problem.
But implying that the majority of implementations shouldn't use FKs unless they have a damn good reason is wrong, I think.
Edge cases do not define the general market. What has to work for them is irrelevant to the vast majority.
And the implication that FKs mean that you don't trust your co-workers, etc is twisting things around a bit. It should be belt and braces wherever you can because, in 99% of environments, a) applications/code/people make mistakes, b) the quality and longevity of the data is not necessarily what a lot of developers are concentrating on, and b) over time knowledge disappears.
I thought the topics in the appendices were excellent. I would encourage you to publish these separately because I suspect a number of people would have not read the paper all the way through. More fool them perhaps, but still.
Thanks for the comments. I just now have to put forward convincing arguments that this is not an edge case but a general case. This will have to come later.
ReplyDeleteI am putting together presentations for OpenWorld covering standard multimedia topics. I would like to have a more general forum for the appendix chapters at a conference, but haven't been able to organise it. I will be campaigning at OpenWorld and maybe able to get a presentation at a later user conference. I feel the Appendix covers the core of what the paper is really aiming for, and you are right, most will likely not get to it, but it was done that way on purpose.
Most of the comments, points raised so far on this blog have been well thought and put together. I hope to be able to put together a further addendum to the paper addressing these points. They have made me think, and some I have had to go back to the text books to do further research.
For those interested in reading alternate comments, this topic has been additionally commented on in Richard Foote's Blog:
ReplyDeletehttp://richardfoote.wordpress.com/2009/08/25/demonizing-foreign-keys-helden/
I am attempting to answer all issues/comments/flames raised on that blog. Later I will include a summary of these comments in this blog.
Richard was invited to be an initial peer reviewer of the paper. I have known Richard for over 14 years, and in 1999 we did a joint paper called Good DBA/Bad DBA. It was my initial discussion with Richard in June that sparked this whole paper/discussion.