Model Your Data Hierarchies With SQL Server 2008

摘要

The manufacturing System behind automobiles; the organization of a country into states, counties, cities, and postal codes; the description of a home entertainment system-what do these things have in common? The simple answer is that each describes a hierarchy. SQL Server~R 2008 supports a new data type, HierarchyID, that helps solve some of the problems in modeling and querying hierarchical information. I will introduce you to this data type by discussing a pattern commonly used in manufacturing known as bill of materials (BOM), or bills. Starting with a brief discussion of BOMs, I will illustrate how this kind of data can be modeled. I will also present an implementation of this model in SQL Server 2005. Then I will show you how the HierarchylD data type can be used to implement the model in SQL Server 2008. Automobiles are amalgamations of many components, such as engines, drivetrains, electronics, and steering. In the United States, our geographic territories are divided into states and are then subdivided into jurisdictions called counties. Counties are then further subdivided in different ways by different agencies. The United States Census Bureau, for example, composes them from Census Tract Areas. The U.S. Postal Service routes mail delivery by Zone Improvement Plan (ZIP) codes. Geographic information systems (GIS) may aggregate census tracts and ZIP codes together to provide users with a familiar spatial reference for an area. A recent trip to a local electronics store to evaluate a replacement home entertainment system pointed to a similar sort of hierarchical system-all the combinations of possible components and options left my head spinning! I wondered how such systems could be modeled and implemented in a database system.