Microsoft Access: Applications and Utilities

Provided by allenbrowne.com, December 2004

People in households and companies - modelling human relationships

How can you receive donations from both individuals and businesses? How do you create a mailing list that is a mix of households and individuals, perhaps even committees or other groups?

These issues arise in any database that handles households, mailing lists, memberships, donors, sales, education, contact management - anything that involves managing groups of people.

Grab the sample database for Access 2000 and later (40kb, zipped). It is not intended as a fully working database - just enough to illustrate how to model complex human relationships with simple database relationships.

What is a client?

The first step is to think clearly about what constitutes a "client" for you. If you make sales to both individuals and companies, you need both corporate entities and individuals in your client table. If you need to create mailing lists for households as well as individuals, your clients must include households and persons.

The first step, then, is to put corporate entities and individuals into the same client table. Open the sample database. tblClient has a yes/no field named IsCorporate - True for corporate entities (companies, organizations, households), False for individuals. The interface uses two different forms, because that is how the user conceives of the data. But because they are all in the one table, you can work with either kind of client when you receive donations, send mailings, write receipts, address households, run searches, or summarize sales per client.

Who belongs with whom?

Now that we have a flexible definition of "client", we need a way to define which individuals make up a corporate entity (the members of a household, the contact people in a business, etc.) In the sample database, tblGroup and tblGroupClient perform that function.

tblGroup.ParentClientID identifies which corporate client we are talking about. tblGroupClient has a matching record for each person in the group. We already created a corporate client called "The Smith Family". Now we create record in tblGroup for The Smith Family, and then add 3 records to tblGroupClient for John, Jane, and Joshua. We now know who makes up this household.

This is completely flexible. Joshua can be a member of two households, so it handles children who spend half their time with each parent. Likewise, Jane Smith can be one of the contact people for Acme Corporation, as well as being a member of her own household, and a donor in her own right.

You can even define many different groups for the one corporate client - e.g. the employees of Acme Corporation, and the Board of Directors of Acme Corporation - each group consisting of different individuals.

Because tblGroup.ParentClientID is not a required field, you can leave this field Null to create an ad hoc group. This is a group that is not a client in its own right. That means you cannot make a sale to them or receive a donation from them, but you can create a very loosely defined group, such as "golfing friends."

Types of group

A mailing list is nothing more than a group of clients. No more tables needed: just create a group, and add the people and corporate clients who should be on the list.

A committee is nothing more than a group of clients. Just create the group, and add the members. The same applies to choirs, sports teams, youth groups, clubs, Special Interest Groups, seminar attendees, ... With just three tables essentially, you have the flexibility to group people however you wish, and in as many ways as you need.

Nesting of groups

This structure permits corporate entities to be members of groups which may belong to other corporate entities. That means you can nest entities inside other entities. This is useful where one company is a subsidiary of another, or where the local chapter of an organization is a member of a national group, which is a member of an international group.

Before you go wild with that idea, SQL (the query language in Access) is not good with nested entities or recursive calls. Research the topic "Bill of Materials" for information on how decompose an assembly into component items, e.g. this sample.

Too flexible?

So, is this design too flexible? A good database structure finds the right balance between the rigidity that prevents bad data (relational integrity, suitable data types) with flexibility that copes with unforeseen possibilities. Database design is an art, not a science. There is an elegance in modelling complex human relationships in something that is so simple relationally.

Part of the secret is to distinguish between structure and interface. We placed personal and corporate clients into the one table, but we designed different forms to interface them. The flexibility is in the structure, the rigidity in the interface. Similarly, on the form frmGroup we limited the combo ParentClientID so only corporate clients are available, since a personal client should not be considered to be a group.

As another example, you might create a special interface for groups that are mailing lists. This form includes the functionality to export the names and addresses of everyone in the group, for merging with Word.

Once you grasp that idea, you will regularly build different interfaces into the same tables. For example, in a purchasing and sales database, you could end up with suppliers, shippers, staff, and customers all in the client table. Just add a ClientTypeID field, and you can provide a different form for each type. Likewise, in a financial database you might have tables for Transaction and TransactionDetail, where you store quotes, orders, invoices, and receipts. Again, you use different forms to interface the different types.

Human relationships are some of the most thorny to model. Hopefully this flexible approach helps keep things simple for you.