Microsoft® Access® Small Business Solutions: State-of-the-Art Database Models for Sales, Marketing, Customer Management, and More Key Business Activities

Appendix C. Resolving Relationship Triangles

In several chapters of this book, we've presented models for managing customer data in a manner that allows a customer to be either an individual or an organization, but not both. As is often the case, implementing a table design to support this business rule leads to hard choices between options because there is more than one viable way to do it, and any approach will involve both the design of the tables and the user interface. In this appendix we take a quick look at two basic approaches to resolving relationship triangles.

Non-Normalized Schema to Support a Less Complex Interface

In the first approach we will demonstrate a way to keep the tables and forms as basic as possible. In doing so, we deliberately opted not to enforce 3^rd Normal Form (3NF); so we have included fields that do not directly describe the main subject of the tables in order to make the interface somewhat less complex. We've selected a generic set of table names in order to make the examplemore generally applicable:

• tblParent

• tblChildtheFirst

• tblChildtheSecond

"Parent" corresponds, in the context of table schema to support customers, for example, to "Customer," while "Child the First" and "Child the Second" correspond to "Individuals" and "Organizations."

Figure C-1 shows the tables and the relationships between them.

Figure C-1. Parent and multiple child tables with two ...