Provided by Allen Browne. Created: January 2009. Updated: April 2009.
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:
Not only do the different types of resource need different attributes, but they are connected through complex relationships (many-to-many, or recursive):
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?
Step back. For any type of resource, there are two main things to track:
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:
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.
The Content tab above allows resources to contain other resources, e.g.:
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:
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:
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.
With this structure, you can search for:
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.
|Home||Index of tips||Top|