Joe Celko's Trees and Hierarchies in SQL for Smarties

Joe Celko's Trees and Hierarchies in SQL for Smarties

The Morgan Kaufmann Series in Data Management Systems
2004, Pages 175-189
Joe Celko's Trees and Hierarchies in SQL for Smarties

Chapter 10 - Hierarchies in Data Modeling rights and content

Publisher Summary

The chapter focuses on hierarchy, which is a specialized type of tree graph. Type hierarchies are useful in modeling entities for a database. The usual attempts by object oriented (OO) programmers to force their model into SQL involve building a metadata model in SQL, in which the tables use a proprietary, nonrelational, auto-incrementing feature that replace a global object identifier and have columns that contain the names of attributes and their values to establish the class hierarchy. These auto-numbering features are a holdover from the early SQL products, which were based on existing file systems. The data was kept in physically contiguous disk pages in physically contiguous rows made up of physically contiguous columns. However, physically contiguous storage is only one way of building a relational database, and it is not always the best option. The chapter uses examples to explain this problem and illustrate why this approach is wastage of resources. Class hierarchies are sets of entities that are defined by common attributes and these sets contain subsets with unique attributes. The idea is to move from the general to the particular. This helps in handling the sets of entities at the appropriate level, based on the shared common attributes at that level. The chapter briefly describes the types of hierarchies, such as generalized hierarchy and disjoint hierarchy. The chapter describes data definition language constraints, such as uniqueness constraints, matrix design in disjoint hierarchy, and representation of one-to-one, one-to-many, and many-to-many.

References (0)

Cited by (0)

View full text