MSDN Magazine - September 2008 - (Page 59)

in the relationship between the different groupings of geo System graphic or census data. Hierarchies exist everywhere, yet implementing them in the context of a relational database frequently proves to Video Stereo Remote Speaker PC Displays Controls System be a challenge. A typical approach is to represent the hier Components archy using a parent/child relationship with one or more tables. While this approach certainly works in many cases, Disk Drives Networking Motherboard it has a few shortcomings. Such solutions must carefully consider how the referential integrity will be maintained. And while querying the depth and breadth of such tables CPU RAM Power Supply was considerably simplified in SQL Server 2005 with the in troduction of recursive common table expressions, writing queries against these types of tables can still be problematic Figure 1 Components within a Home Theater System when joins against many tables are required. system shown in Figure 1. It might be easy to say that each item A Bill of Materials Problem shown here represents an entity. While this is true, it becomes dif A few years ago I was working on a system being developed by a ficult to represent all of the possible home theatre systems that a manufacturing company to help their dealers specify the compo company might sell. What if some systems use a digital video re nents needed to build centerpivot irrigation systems. The software corder (DVR) instead of a PC? What about consumers who don’t produced a list of components needed to custombuild the desired want a radio tuner? pivot (the totality of a centerpivot irrigation system is simply re Variations like these are precisely why a BOM system works well ferred to as a pivot within the industry). The required components as a pattern for hierarchical data modeling problems. It allows for were determined based on geography, soil type, and the intended a complete system to be composed from many different subbills crops planted in the areas to be covered as well as the hydrologic and subsubbills until you eventually get down to individual parts and structural considerations of the device itself. in a tree data structure. At the root of the tree is a complete system Underpinning the solution would be a SQL Server database. The represented by a single bill. The first level of branches is generally purpose of the database was to store information about the com comprised of bills as well. The list of subbills continues down the ponents available to build the pivot. However, when we generated hierarchy until a bill consists only of a list of physical system parts, the specification for manufacturing, we needed to identify those and these parts become the leaf nodes on the tree. components as BOMs. So what entities do we have in the system? There Some bills represented a collection of physi are bills that contain only parts (parts lists), and Part List Bill Part cal parts that would be assembled into a system there are bills that contain other bills. component. For example, every pivot needed A part has a description and a cost. (Of course, Part a pump to draw water from a well into the sys it could have many other attributes, but let’s Part Part List tem. That pump might be electrically powered, keep things simple for illustration purposes.) A meaning it needed a transformer and fuse box, parts list’s attributes might include a part and the Part too. Or the pump might be fuel powered, mean quantity of that part required. It is important to ing it needed a tank, a fuel pump, and hoses to separate the quantity of a particular part need connect the pump to the tank. In either case, Figure 2 Relationship of Entities ed from the part information itself. If you fail to the required parts for the pump would be listed do that, you may end up having many duplicate in a pump bill. instances of a part that is based only on the difference in the quan The bill for a complete pivot would include a collection of other tity used. Normalization rules suggest that this is an inappropriate bills. For example, a standardized pivot might consist of a tree of design in most cases. bills for the pump, another tree of bills for the spans of pipe used A bill will have a description, a way to associate it with its par to deliver water, and bills for any other equipment needed to build ent bill, and an associated parts list. Figure 2 represents the set of that pivot system. entities and their relationships. While relational databases are great at representing most rela Using Entities to Understand the Problem tionships, they are not well suited to working with manytomany The first question you should ask is: what are the entities you entity models. It is not that the physical tables cannot handle the need to be concerned with in representing a hierarchical system data; rather, it is difficult to create appropriate referential integrity and what attributes might those entities have? The trick is to not constraints between the tables. Such relationships also lead to com be drawn into the semantics of the individual elements in the un plex queries. This problem is easy to fix, however, when you first derlying system you are trying to model. address the logical design of the tables. You need to insert a table For example, consider the case of the PCbased home theater between the part list table and the bill table. That table will simply msdnmagazine.com September 2008 59 msdnmagazine.com

Table of Contents Feed for the Digital Edition of MSDN Magazine - September 2008

MSDN Magazine - September 2008 Contents Toolbox CLR Inside Out Data Points Advanced Basics Office Space Cutting Edge Hierarchy ID New Features for Microsoft SQL Server 2008 Prism Data Services Advanced WPF Test Run Security Briefs Foundations { End Bracket }

MSDN Magazine - September 2008

For optimal viewing of this digital publication, please enable JavaScript and then refresh the page. If you would like to try to load the digital publication without using Flash Player detection, please click here.