Introduction to Collection Database Design


Bill Paine

Florida Museum of Natural History

Does your collection need a database?


What should a collection database do?


Define your collection

Develop a narrative
"Our collection is made up of over 10,000 art objects. These objects are of varying media and types, and were created by artists from many places during many different time periods. Information is cataloged on the artists as well as on the objects themselves. Each object has a media type, is either 2-D or 3-D, a date/time period, school of art, condition at time of accession.." etc.

Participation of all involved parties

Be as complete as possible
art objects, artists, related publications, loans, condition reports, etc.

Create a Schema

List attributes - art collection example:
- media
- time period
- 2-D or 3-D
- artist
- condition at acquisition
- condition updates
- loans
- publications

Schema Diagram

Redundancies

Revealed

Where redundancies can occur

locality descriptions

names
people
scientific names
methods or tools

media types

etc.

Relationships

Complete data for an individual artist get entered into the Artist table only once. Each artist record gets a unique identifier, the Artist_ID. Then, when entering art objects into the main catalog table, an artist can be entered by simply entering the appropriate Artist_ID, eliminating redundancies and dramatically reducing the possibility of input error.

Basic Relationship Types


One to Many

A single mammal specimen can belong to only one genus. A single genus can be represented many times within the collection catalog. Each genus is unique in the Genera table, but not in the Mammal Catalog table.

Many to Many

A given butterfly species can be found in more than one state and a state can be home to more than one species of butterfly.

Table population example

Query examples

One to One

A relationship that is single-valued in both directions is called a one to one relationship.

A department has only one manager, and a manager can manage only one department.

Entity Relationship Diagram

Once the relationships between the tables are sorted out, then the tables can be completed by adding all remaining attributes.

When the schema includes all of the main tables with their relationships defined and attributes added, then the project is ready to become electronic.

Development of the schema is extremely important in order to better understand what functions a collection database will be expected to perform.


The knowledge gained here:
- will greatly enhance a search for software to purchase
- is the basis for any custom written software

Graphical User Interface (GUI)

GUI Examples

The user sees the borrower name, but the borrower ID is actually inserted into the main table.

GUI Examples

GUI Examples

Relationships behind the GUI form shown on the last slide

GUI Examples

Many to Many relationship

GUI Examples

GUI Examples

GUI Examples

GUI Examples

GUI Examples

GUI Examples

GUI Examples

Decisions - When Design is Complete


Things to consider:
- collection size and type
- the amount of data captured for each collection object
- frequency of use for the collection
- public access to any or all collection data
- funds and resources available
- timeline

Decisions - When Design is Complete


who will be primary and occasional users?
- curators and collections staff
- general public
- outside researchers
- others

Build it or Buy it?

Time to decide whether to build a custom application or to purchase already built "off the shelf" software.

Custom-built Application


Benefits:
- get exactly what you need/want
- can gain the ability to modify application in the future

Custom-built Application


Costs:
- monetary cost -- paying developer(s), either on your staff or outside consultants, to create and support your application. Costs can be ongoing or cyclical, depending on needs and budget

- some staff time will be spent participating in debugging the new application

Already-built "off the shelf" Application


Benefits:
- get a "turn-key" product, ready to use "out of the box"***
- vendor support of some form should be available

Already-built "off the shelf" Application


Costs:
- up front monetary costs can be quite high
- support can be expensive, AND required in order to recieve product updates

- quality of support varies widely
- vendor can go out of business and/or retire your product
- may not get what you want/thought you were going to get***
- usually little or no ability to make modifications
- some staff time will be spent participating in debugging the new application

Typical development cycle - "off the shelf"


Typical development cycle - "custom"

A Final Note

Either method requires database users to take the time to learn a new system and to "debug" problems

Further Information