PATTERNS OF DATA MODELING- P8: Models provide the means for building quality software in a predictable manner. Models let developers think deeply about software and cope with large size and complexity. Developers can think abstractly before becoming enmeshed in the details of writing code. Although models are beneficial, they can be difficult to construct. That is where patterns come in. Patterns provide building blocks that help developers construct models faster and better. | 16 Chapter 2 Tree Template Tree root Node 1 nodeName unique parent child a Globally unique node name I I I I I I b Unique node name within a context Figure Simple tree UML template with node names. There are two variations of the template globally unique names and names within a context. Figure Sample tree with node names that are unique within a context. IDEF1X Template Figure restates Figure with the IDEF1X notation. The following are foreign keys rootID references Node andparentID references Node. Tree i 1 1 Tree treelD Node r treeID Node rootID FK rootID FK nodelD 1 nodelD 1 L parentID FK nodeName 1 1 L parentID FK nodeName a Globally unique node name 1 I b Unique node name within a context Figure Simple tree IDEF1X template. Figure uses existence-based identity which is my preferred approach for database design. See Chapter 16. Existence-based identity means that each entity has its own artificial identifier such as an Oracle sequence or a SQL Server identity field as a primary key. Thus the primary key of Node is nodelD and not for example nodeName Figure or Simple Tree Template 17 parentID nodeName Figure . Although I favor existence-based identity the templates in this book do not require it. In Figure the AK notation denotes a candidate key that is a combination of attributes that is unique for each record in a table. No attribute in a candidate key can be null. All candidate key attributes are required for uniqueness. Figure defines parentID nodeName as a candidate key but one record is an exception. The root node for a tree has a null parentID and a candidate key cannot involve a null. Most relational DBMSs treat NULL as just another value and do not strictly enforce candidate keys so the definition of a unique key for parentID nodeName does work. I verified this for SQL Server and expect that it would work for most relational DBMSs. Alternatively you can
