DBAs today are learning to adapt to operational changes in organizations that are working harder to serve the needs of their customers, clients, or members. Organizations must collect and manage different kinds of data in more complex ways than they might have in the past. I recently worked with a DBA who was facing the daunting task of changing her database design to better meet the needs of an increasingly complex membership.
Becky is a part-time DBA and database developer for a local nonprofit organization that gets its funding through the dues that its members pay. The organization maintains a member database so that it can sell memberships at different levels to new members, retain existing members, and communicate with members by mail. The organization also provides benefits and services to its members—and to the community at large—in the form of concerts, musical entertainment, and music classes. Members receive membership cards that they use to get these benefits for free or at discounted prices.
Like many DBAs these days, Becky shoulders a variety of responsibilities, so the time she can spend with database maintenance and development is limited. Becky designed the current membership database several years ago, but her organization has outgrown the database's capabilities. The number of members has grown, but more importantly, the makeup of the membership and members' family structures no longer fit the old design.
Originally, the database's Members table was set up to accommodate a traditional nuclear family unit of one or two adults and multiple children who share the same surname and live at the same address. However, our modern culture now recognizes that a family can include a variety of combinations of adults and children living under the same roof, sometimes with different last names. In my own home, we have this situation. My husband and I use different last names. It's not unusual for us to receive mail with the following name variations:
Michelle & Michael Poolet
Michael & Michelle Reilly
Mr. & Mrs. Michael Reilly
Mrs. & Mr. Michelle Poolet
Even though all these variations have the same mailing address, they're all incorrect. Most mailing databases don't accommodate more than one surname for an address, and one unfortunate result is duplicate mailings. My husband and I receive duplicate copies of many mailings, and the companies that are paying for these glossy brochures, catalogs, and advertisements are wasting a lot of money. Few organizations have an unlimited budget for mail campaigns, so reducing duplicate mailings is an added benefit of the proper design of a membership database. Becky has to redesign the membership database so that her nonprofit can more effectively serve its diverse membership, and if in the process she can reduce the number of duplicate mailings her organization sends out, she'll be very happy.
According to Webster's New World Dictionary of the American Language, the first definition of family is "a household." Taking this cue from the dictionary, Becky can approach designing a membership database by treating a family as a household—a collection of adults and children who reside at the same address. This designation doesn't attempt to define personal relationships such as parent, child, sister, brother, or grandparent. The word household merely implies that these people, who may or may not be related to each other, live in the same place. Becky's organization will enforce a business rule that says a membership is assigned to a household.
While Becky is redesigning the membership database, she and her colleagues on the executive staff decide to modify the levels of membership. Previously, the organization offered only two membership levels (at two different prices): individual and family. A family membership included as many as four people in the same household with only one name on one membership card. The organization's staff has decided to offer a more flexible variety of membership levels:
- Individual—one adult member. The nonprofit issues one membership card in the member's name.
- Individual + One—one adult member and a guest. The nonprofit issues one card in the adult member's name. Upon request, the organization will also issue a permanent guest card in the guest's name.
- Household—two adult members and unlimited children and grandchildren. The nonprofit issues two cards, one in each adult's name.
- Household Plus—two adult members, unlimited children and grandchildren, and a guest. The nonprofit issues two cards, one in each adult member's name. Upon request, the organization will also issue a permanent guest card in the guest's name.
The least expensive membership is the Individual; the most expensive is the Household Plus.
Figure 1 is a conceptual data model of part of the new membership database, which is based on the organization's new household business rule. According to the rule, membership at the different levels is assigned to a household, which is identified in the Household table by geographic location—street address, city, state, and ZIP code. Each household can have any number of residents of varying types, which the ResType table identifies as primary adult, secondary adult, guest, and child. The Resident table includes fields that identify residents by name (first and last, both mandatory) and by the roles they play in the household (ResRole)—spouse, parent, grandparent, life partner, and so on. The Household table contains a field for the membership activation date (DateJoined) and a field for household contact information—the main telephone number and email address. Each member in a multiperson membership can also have a phone number and email address listed in the Resident table.
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.
