Microsoft Access: Applications and Utilities

Provided by Allen Browne.  Created: January 2009.  Updated: April 2009.

Library resource example

A library may have different types or resource: books, journals, movies, music, electronic references (websites, server files), and so on. Each of these has its own characteristics:

• Books have a title, publisher, copyright, and one or more authors/editors.
• Periodicals (journals, magazines etc) have issue numbers and/dates.
• Music has composers, producers, vocalists, bands, recording dates.
• Movies have producers, directors, actors, narrators etc.
• The content of some resources (such as websites) depends on the date accessed.

Not only do the different types of resource need different attributes, but they are connected through complex relationships (many-to-many, or recursive):

• Resources within resources: one book may be part of a set (e.g. a volume of an encyclopaedia), or may contain several books (e.g. a trilogy printed as one volume), and each one may contain several articles. The same article may even be printed in several books.
• Authorship: a book may be a collaborative effort (many authors.) In fact, each article in a book can have multiple authors.
• Music: A CD may contain multiple tracks by the same artist(s), or it could be a compilation of tracks by different artist(s.) These artists may be informally combined (e.g. an ad hoc duet), or formally grouped as a band (but allow that the band members may change over time.)
• Movies/documentaries may be part of a set, or contain multiple segments (each with their own narrators/actors), or be published as multiple movies on one disk.

Then you are going to add keywords, subjects, categories, and indexes (such as ISBN.) And you may need to track multiple copies of each resource, possibly at different locations, or in different kinds of media.

How do you create a database that has the right combination of rigidity (correctly normalized), flexibility (copes with all the above), efficiency (search speed), and a very simple interface?

The core entities

Step back. For any type of resource, there are two main things to track:

1. The resource (i.e. the book, periodical, album, movie, ...)
2. Who created it (authors, publishers, singers, actors, ...)

One resource can have several creators, and one creator can make many resources. This many-to-many relation means a junction table between the two.

For each resource, we need to know what kind of thing it is, so we need a table of resource types (book, movie, etc.) For each person involved in creating the resource, we need to know what their role was (author, publisher, director, etc.)

These five tables (at right) cope with all these resource types:

• For a book, the junction table contains a record for the publisher and a one or more records for the authors.
• For a movie, the junction table contains a record for the producer, one for the director, and as many as needed for the actors.
• For an MP3, the junction table contains as many records are you wish to track the singer, producer, musicians, etc.

Interface

The main form is bound to tblResource, with a subform to show who created the resource (one row per creator.) The other tabs are discussed below.

Recursive resources

The Content tab above allows resources to contain other resources, e.g.:

• An encyclopaedia contains volumes that need to be indexed individually.
• A periodical contains articles that you must index individually to be searchable.
• A music CD may contain tracks by different artists (so individually searchable.)
• A DVD may contain multiple episodes/movies.

We propose a self-join: tblResource will have a field indicating which resource it belongs to. If it is contained in another resource, this field will contain the ResourceID of the parent resource; otherwise the field will be Null.

To do this, we add tblResource to the Relationships table a second time. Access aliases the second copy as tblResource_1. We drag tblResource_1.ResourceID onto tblResource.ParentResourceID to create the relation, so ParentResourceID can contain only a valid ResourceID value.

This simple structure now copes with all these kinds of resources — even when they contain other resources. On our form we provide a tab to enter the items in the resource:

Your copies

A library may have several copies of the same resource, and you need to track each one individually so you know who has which one. This implies a one-to-many relation between the resource and the copies. If people borrow them, you lend a particular copy (not the resource itself.)

For each copy, you may need to record where it is normally kept (its location), and the media format for that copy. For example, a book may be in hardcover, paperback, or PDF. Some copies of a movie might be on VCR, while others are on DVD. We therefore have these tables:

Keywords and searches

Each resource may be associated with multiple keywords, categories, or subjects. In some scenarios you would want to define set subjects or categories (in a table), with a junction table between subjects and resources so one resource can be assigned to many categories.

In other cases, it might be better to allow the user to choose any keywords to associate with your resources — not merely predefined ones. This would imply a simple one-to-many relation between resources and keywords (where one resource can have multiple keywords.)

Which one is better will depend on your needs, but it might be better not to have both. If you have a related table of fixed categories as well as a related table of free-form keywords, there is no clear place to look.

A compromise is to allow free-form keywords in the related table, but index them. You can then use a combo box to enter the keywords with all the advantages of the fixed categories, but leave the combo’s Limit To List property set to No so the user can type any keyword they want.

Executing a search

With this structure, you can search for:

• Any resource with a keyword of “horror”, since all resources are in the one table.
• Just books (filter the resource type.)
• Anything where Clint Eastwood was involved (regardless of whether his role was actor, director, or script writer), since creators are in one table.
• Any articles jointly created by Crick and Watson (and any others) in a date range.
• The sets or journals that contain any article with DNA as a keyword or part of the title.
• Myriads of other combinations.

Conclusion

When you started creating a library database, you may have thought in terms of title, author, publisher, and keywords. The exceptions — the things that don’t fit into those neat packages — force us to step back and think more broadly. As we did so, we noticed similarities between the various resource types, and concluded that the resources and their creators were the two many entities.

This example illustrates how thinking about the data may lead you to a different kind of structure. Crucially, it must be: correctly normalized, flexible enough to cope with all types, and very simple to query. Within the boundaries of good normalization, the ability to find the simplest solution that copes with all cases is what marks a good developer, and makes database development more of an art form than a science.