A well-designed database does not store redundant information, but has a number of relationships between tables. The typical relationship is a one-to-many relationship. For example, if you are designing an application that handles sales orders, there can be many items on one single sales order, but one specific item can only be part of one sales order. Some of the information on a sales order, such as the address of the customer, is per order, while other information, such as the item number, is per item. In a well-designed database, with no redundant information, this means that the information on a sales order is stored in two tables: a header table with the general order information and a lines table with the information about each item. There is a one-to-many relationship between the tables. However, users of the application need to view information from both tables at the same time.
The following illustration shows the header and line information.
Although this looks like an ordinary form, it is actually two forms.
The main form is the one side of the one-to-many relationship. In this example, it is based on the Sales Order Header table. The subform is the many side of the relationship and is based on the Sales Order Line table. When you select a sales order header in the main form, the subform is updated to display only sales order lines that are related to this sales order header. There is a link between the main form and the subform to keep the information synchronized.