Object Oriented Databases Complex relationships
Complex relationships: SQL is a very rich language for finding objects by attribute using a single statement. An example query by attribute might by "SELECT FROM CUSTOMERS WHERE LAST_NAME = 'Jones'". Query engines and b-tree indexes can make queries like this perform very well. Unfortunately, querying by attribute is generally the only mechanism offered by SQL databases to identify and fetch objects. If an application needs to perform multiple queries based on relationships, fetching objects by attribute becomes excruciatingly slow and cumbersome. An example query by traversal would be to find all the orders that a particular customer has placed.
In a software application, a Customer object would simply contain pointers or references to a group of Order objects. But a Customer table in a SQL database can’t have a variable number of columns. Instead, identifiers (called primary keys) are added to the Customer table to uniquely identify the customers. Foreign keys are added to Order table that reference these rows.
One can then join the two by querying by attribute (“SELECT FROM CUSTOMERS,ORDERS WHERE ORDERS.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID”).
This is such a common and well-known practice that it can be taken for granted. In fact, joins like this are quite expensive. First, it requires an extra column in both the CUSTOMERS and ORDERS table. Also, to perform properly, they generally require that the primary key column be indexed, which can be a very high cost in terms of storage, memory, concurrency and insert performance.
The cost of joins becomes even more expensive when one considers many-to-many associations, like orders can include many products and products can be included in many orders.
In a SQL database, these require a whole separate table (called a “join” or
“junction” table) to represent the relationships. These intermediate tables
become much larger than either of the two tables at the ends of the associa-
Not only do the primary keys of the two original tables require indexes, an index is required on at least one column, if not both columns, of the join or junction table.
Now consider a network of objects or elements, a network being a tree where each child can have multiple parents. Examples of a network of elements might be a road network or a network of inter-related messages. These kinds of complex structures are generally implemented in “connectivity” tables. These tables are like join tables, but even more inefficient, inadequate and unnecessary.