The modeling tool I used for this article is Sybase's PowerDesigner 10.0. In Figure 1, the foreign keys don't appear as attributes. Instead, the relationships imply the foreign keys. For instance, HHID, the primary key of the Household table, will become a foreign key in the Resident table when I generate the physical data model (the step that creates the tables in preparation for generating the T-SQL code to create the database). So if a family moves to a new house, Becky's staff can easily make the change in the Household table because the primary identifier of the Household table is the identity field, HHID. None of the Resident records would be affected because they're associated with the Household table through HHID. If a family grows and needs to upgrade its membership from, say, Individual + One to Household or Household Plus, Becky's staff can make the change by simply changing the value of MemCode in the Household table's MemCode field. (MemCode, the primary key of the Membership Level table, will become a foreign key in the Household table when I generate the physical data model and the T-SQL code.)
This arrangement can accommodate a host of complex living arrangements. Two adults who have different last names and live in the same household with no children can purchase an Individual + One membership, and each can have a membership card imprinted with a correct name. A couple who may not share the same surname can partake of the nonprofit organization's services and include their children or grandchildren simply by purchasing a Household membership. If only one person in a household of several people wants a membership, that person could purchase an Individual membership. A multigenerational household that consists of Mom, Dad, their divorced adult daughter, and her children might avail themselves of the Household Plus membership.
Once Becky has created these tables in the new membership database, she can create an indexed view on the join of the Resident and Household tables, which materializes the joined data, making data retrievals easier and quicker for her staff at the membership desk. (For more information about indexed views and materializing joins, see my June 2004 article, "Materialize Your Views," InstantDoc ID 42331.) Almost everyone who will be working with memberships will want to see data from both the Resident and the Household tables at the same time, so this indexed view will save Becky's nonprofit organization hundreds of join operations on these two tables every day. For additional information about the use and benefits of indexed views, see Kalen Delaney's article "Introducing Indexed Views," May 2000, InstantDoc ID 8410.
Managing a membership database is a full-time job in any company. For a nonprofit organization, an efficient database is especially crucial to reducing duplication in mailing campaigns and offering its members the highest possible level of service. Whether a nonprofit thrives or fails depends on its mem-ber support. With this new database design that accommodates all manner of family arrangements, Becky can help her organization increase service to its membership.
End of Article
Prev. page
1
[2]
next page -->
