The Hermit Hermit's Free Library  Database Design Cases

Motorcycle Club Membership Database

This is the first in a series of database design case studies. Consisting of just two tables, it is the most basic case presented.

System Analysis

The secretary for the Canadian Vintage Motorcycle Group has asked you to design an Access database for the club.

The club needs to print a club roster which includes the names, addresses, and telephone numbers of all its members as well as the make, model, and year of each member's vintage motorcycles.

Design a suitable database and diagram it to show the fields (including keys), the tables, and the relationships between the tables.

Fields

The first step in database design is to determine all the necessary fields and segregate them into groups that mirror the natural order of the real world.

Field names for relational database tables

Tables

When the fields have been grouped into their natural order, each group represents a table.

Diagram of tables in a relational database

Key Fields

Each record in a table must be uniquely identified by its value in the table's key field.

Diagram of relational database tables showing key fields

Relationships

The next step is to identify the relationships between the tables. In this example there is a one-to-many relationship between members and their motorcycles: one member may have many motorcycles, but each motorcycle can only belong to one member.

Diagram of relationships between tables of a relational database

Primary & Foreign Keys

When two tables have a one-to-many relationship, the relationship is created by placing values from the "one" table's key field into a field of the related records in the "many" table. When this is done, the key in the "one" table is known as a primary key and a foreign key in the "many" table.

Diagram of relationships between tables of a relational database showing primary and foreign keys

The Rationalized Model

The final result is a relational database model. When a database model satisfies all the technical requirements it is said to be rationalized.

The objective of rationalizing a database structure is to eliminate redundancy in order to save disk space and to simplify updates by insuring that every piece of information exists in only one place. The only fields that repeat are the foreign keys.

A rational database model must satisfy these four rules and pass these three tests.



Complete diagram of a relational database