One of the toughest SQL problems is supporting recursive (bill of material) relationships. For example, a part consists of many other parts; each part can go into many other higher level parts. Some DBMSs have recently added functions to provide support for such relationships. Can BOM recursion be supported in basic SQL, i.e. SQL that does not have special support for recursion? Does one have to go to application code to achieve support for it?
If you design your recursive relationship using the adjacency model, where each row has a foreign key to its parent row, then the answer is no, there is no recursion in basic SQL. There might be something in a recent SQL Standard, but what really matters is whether it's included in real-life database systems, and as far as I know, only Oracle among the established vendors supports it, through a proprietary SQL extension.
If you design your recursive relationship with the nested set model, however, then the answer is yes. First off, let me caution you that the nested set model is not for the faint of heart, but it is well worth the effort if you have recursion to many levels or to an unknown number of levels. Have a look at the SearchDatabase article Trees in SQL by Joe Celko.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.