SQL foreign key access and tree structures

Relation  in relational database theory means table  and nothing else, table with rows, columns and cells. Relational databases are tables, SQL is the programing language for manipulating and accessing those tables. The relational database is based on the theory of sets, this is a mixed blessing. On the positive side the theory of sets is mathematically proven and that is a good thing in itself at least it says the foundation is sound. But databases in applications are more than sets, a database is a model of the real world the application is supposed to support. E.g. sets do not have structure or order, the real world has. The structure the relational database have is the table structure and simple ‘relational links’ between tables, that can be used to restrict creation or deletion of rows in linked tables. Unfortunately SQL do not understand these links well, you cannot implicitly access linked data or show hierarchies.

Access via foreign keys.

Something I miss in SQL is the ability to walk through children and infer through parents via foreign key links. (You can to a certain extent compensate for these shortages with database views and virtual columns.)

For this database I would like to be able to say:

1. select sono, customername , quantity sum-throu  SOlines as qty for SalesOrder;

1. customername is the customername of the SalesOrder infer through the customerid foreign key link and in case of ambiguity can be qualified as SalesOrder.customername . Customer is a ‘link owner’ of SalesOrder.
2. sum-throu is an infix function that sums the quantity of the SOlines children of the SalesOrder through sono foreign key link. Some other throu functions are max, min & mean. SalesOrder is a ‘link owner’ of the SOlines.

Compare this with the more correct SQL:

1. select sono,customername, sum(quantity) as qty for SalesOrder left join Customer on customerid, left join SOlines on sono group by sono, customername;

I do not like specifying things the computer should be able to figure out. Getting rid of explicit joins is an advantage, even trivial joins are complex for most people. Ever since the introduction of foreign key links in DB2 I wondered when SQL should include access via foreign key links. I have thought of creating a preprocessor in MySQL proxy, but we still use MyISAM storage engine which do not support foreign keys. MyISAM is very convenient for BI system, and historically it has been the fastest storage engine, with the latest MySQL versions this may not be true anymore, I will  have a look at InnoDB when MySQL 5.6 is released.

(Foreign keys are the SQL feature I miss the most in MyISAM. Other features I miss in MySQL are computed/virtual columns and materialized views.)  

Tree structures.

The support for tree structures in SQL is at the best crappy.Good tree structure support includes:

1.  Ability to go up and down the tree, starting from the top or the bottom.
2. Stop traverse the tree at certain nodes or exclude nodes from the result tree.
3. Tree level indicator so you know where in the tree you are.
4. Ability to intercept updating if loops are found in the tree structure

Tree structures are very useful but unfortunately also very complex, so they are ‘under-utilized’. Simple and powerful support for tree structures including the bullets above would take SQL to a new level. Tree structures are a big topic that deserves a post of its own. I end this post with a link to a post  describing a tree structure we use.