The flexibility is in the structure, the rigidity in the interface. We placed personal and corporate clients into the one table, but we designed different forms to interface them. Part of the secret is to distinguish between structure and interface. There is an elegance in modelling complex human relationships in something that is so simple relationally. Database design is an art, not a science. 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. For a more complex treatment, see Trees in SQL or Nontraditional Databases by Joe Celko, or Managing Hierarchical Data by Mike Hillyer. Research the topic "Bill of Materials" for information on how decompose an assembly into component items, e.g. 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.īefore you go wild with that idea, SQL (the query language in Access) is not good with nested entities or recursive calls. That means you can nest entities inside other entities. This structure permits corporate entities to be members of groups which may belong to other corporate entities. With just three tables essentially, you have the flexibility to group people however you wish, and in as many ways as you need. The same applies to choirs, sports teams, youth groups, clubs, Special Interest Groups, seminar attendees. Just create the group, and add the members. No more tables needed: just create a group, and add the people and corporate clients who should be on the list.Ī committee is nothing more than a group of clients. 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Ī mailing list is nothing more than a group of clients. This is a group that is not a client in its own right. the employees of Acme Corporation, and the Board of Directors of Acme Corporation - each group consisting of different individuals.īecause tblGroup.ParentClientID is not a required field, you can leave this field Null to create an ad hoc group. You can even define many different groups for the one corporate client - e.g. 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. Joshua can be a member of two households, so it handles children who spend half their time with each parent. Now we create record in tblGroup for The Smith Family, and then add 3 records to tblGroupClient for John, Jane, and Joshua. We already created a corporate client called "The Smith Family". tblGroupClient has a matching record for each person in the group. TblGroup.ParentClientID identifies which corporate client we are talking about. 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. 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. The interface uses two different forms, because that is how the user conceives of the data. tblClient has a yes/no field named IsCorporate - True for corporate entities (companies, organizations, households), False for individuals. The first step, then, is to put corporate entities and individuals into the same client table. If you need to create mailing lists for households as well as individuals, your clients must include households and persons. If you make sales to both individuals and companies, you need both corporate entities and individuals in your client table. The first step is to think clearly about what constitutes a "client" for you. It is not intended as a fully working database - just enough to illustrate how to model complex human relationships with simple database relationships. Grab the sample database for Access 2000 and later (40kb, zipped). These issues arise in any database that handles households, mailing lists, memberships, donors, sales, education, contact management - anything that involves managing groups of people. 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? People in households and companies - modelling human relationships Microsoft Access tips: People, households, and companies - modelling human relationships Microsoft Access: Applications and Utilities
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |