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.
Prev. page
[1]
2
next page 
