Planning a database for your shell collection

by Gavin Malcolm

Many personal computers have database software which provides the functions for building a shell collection catalogue and the preparation of reports, labels and listings from the data. Today's database software uses relational database techniques which permit the linking of data records and give improved cataloguing, retrieval and report production features.

The first key decision in planning such a shell database is to decide the scope of the database. If you collect just one shell family or two, e.g. Conidae and Olividae like me, then you can plan a database which includes completed data on all cones and all olives and then have a second database with details of your collection. This allows you not only to list details of your collection but also to have information on the family of shells, list the species which you are missing, etc.

If you collect all families and species, then the amount of work entering data for all the shells will be a lifetime task. Use the data structures below but only load the data for the species which you have in your collection.

A second decision is the description field for the shell entity. If you limit yourself to 255 characters then you can ask the database engine to search through the descriptions for selected words, e.g. blue cones. If you choose the longer description, with a memo field, then you will probably be unable to search within the descriptions. Decide why you want descriptions and how much data you are prepared to enter.

Getting started

My experience across several stages of development has shown that the best data structures are:

Shell Species Entity Database with an entry for each distinct shell type in terms of family, genus, species, subspecies, colour form, author, etc. This is the master database with a specific unique number (shell number) allocated by the system to each entry.

Many texts would advise you to define the shell record down to the level of species and subspecies but I would suggest that you go further and include named colour forms if you have a specialised collection.This will give you the opportunity to describe colour forms from the different geographies in the master database.

Collection Specimen Entity Database with an entry for each individual shell in a collection. Each entry for a specimen contains the shell number used in the main database to describe the shell type.

With these two databases, you can have more than one specimen, or none, for each shell record. You can ask the software to create a join and the system will then synchronise and maintain the two databases in harmony. Just watch that you set the delete rules to avoid deletion of the master shell record when you delete a collection record.

You can design a form or screen to enter the shell species data and your collection data from one screen. You can find out how many shell specimens are in your collection for a given species or colour form; you can display all the available species data associated with each specimen; you can list your collection; print labels of all sizes; or generate a list for specimens in your collection of a species with sizes etc. to try to identify that shell with the missing label!

If you have entered into the shell species database the details of the complete family or genus, then you can print want lists and begin to have a source of useful data about your chosen specialist shell families.

If you are starting out then I advise version one of your database be limited to one species or a small subset of your collection. You can add more data later.

Building on the base

My next phase of development (version 2) was to add a Synonyms Entity Database with an entry for each synonym used in the shell families. Each entry contains a shell number link to the master species database. You can have more than one synonym or none for each shell type. Look up a shell and you see all the synonyms. Search on a synonym and you find the shell.

This proved to be a real bonus when I was travelling and I could unravel all the names local dealers were using to make their specimens unique. But a synonyms database is only of real value if you have loaded data on the complete family or genus.

With a master database of all conus species, subspecies and colour forms together with synonyms and collection details, I was able to take my knowledge with me on my portable PC Thinkpad and this served me well for several years.

Pictures

The ability to take digital pictures is now becoming more common, so I decided that it would be nice to have pictures of the specimens in my collection and maybe get some pictures of the family species members which I was missing. Then I could refer to the database on my travels when internet access to the web shell picture galleries was not available.

Most database applications will allow you to define a picture file and include it in your collection database entry as a field so that each time you look up a specimen the picture is there on the screen. Just watch what size of picture you include since a copy is stored in the database file. This was a great addition to my collection catalogue records (version 3). A picture is worth a thousand words and I was able to show my collection to my friends on my travels.

I could even define a picture field in the master database and capture a picture of the species which I did not have in my collection from copyright-free resources on the web. So I ended up with a picture in the master database of the shell and a picture of each specimen in the collection database.

This was very successful for I could compare my collection specimen with one available for exchange or sale or I could validate any new species that I was being offered.

The one restriction was that while one picture for each specimen in the collection was sufficient, I began to want more than one picture of a species in the master database. Having colour forms entries in the main database was a great advantage since I could at least have one picture for each subspecies and named colour form.

Completing the data entities

Given that I focus on just two families, I have now added in version 4 a Prices Entity Database with prices of specimens. It allows me to enter the price for a specimen of a given size, quality and location. I started by adding the prices of the species which I had on my want list and gradually built a reasonable set of price references for my specialist families.

Publishing internet pages

Recently, in version 5, I began to add a Visual Basic program which takes exported copies of my databases and automatically creates web pages, one for each cone entry, with pictures and details of my collection specimens. These shell pages together with index pages provide a powerful reference, identification and catalogue retrieval tool. To do this, I had to separate my pictures into a picture entity database with an entry for each picture, including its file location/name and links to the specimen and species master database.

The challenges

Decide your initial scope. Plan an overall design and add data as you build experience. Complete each version before adding more entities.

Keep it simple … if you just want a catalogue then you just need a master record database and a collection database with pictures in your collection database.

If in doubt, make the data field as wide as you need; the pictures dictate the size of storage space.

To enter data about a family choose a good publication with a definitive list of species. Don't cry if, like me, you have just finished an Olividae database according to Petuch and Sargent in the Atlas of Living Shells to find Tursch and Griefender produce a new work, Oliva Shells, which redefines the world of Olivas.

Sample data descriptions

The data fields will have to be keyed for each species or colour form. Where possible use drop-down lists to ensue the quality of repetitive content such as family names, geography areas, etc.

 

My shell species database
Record no.	Numeric	allocated incrementally by the system
Type	Alpha(20)	land, marine, freshwater, etc.
Class	Alpha(75)
Order	Alpha(75)
Superfamily	Alpha(75)
Family	Alpha(75)
Genus	Alpha(35)
Subgenus	Alpha(35)
Species	Alpha(35)
Subspecies	Alpha(35)
Colour form	Alpha(35)
Author	Alpha(50)
Year	Numeric
Common name	Alpha(75)	popular name for the shell
Description	Alpha(255)/Memo
Geographic range	Alpha(255)	description of countries, oceans, etc.
Geography	Alpha(30)	drop-down list of standard ocean geos
Habitat	Alpha(50)
Size max (mm)	Numeric
Size min (mm)	Numeric
Size range description	Alpha(255)	notes on size
Remarks	Alpha(255)
Picture	Picture Field

 

 

My collection database
Collection no.	Numeric	allocated incrementally by the system
Shell record no.	Numeric	link field to master record database
Catalogue no.	Alpha(15)	used to identify specimen on label, shell, etc.
Date catalogue	Date	allocated by computer
Storage reference	Alpha(35)	physical location
Quality	Alpha(15)	beach, F+, etc.
Status	Alpha(10)	hold, exchange, sell, etc.
Size (mm)	Numeric	alpha for families that require two dimensions
Description	Alpha(255)	details about specimen
Date obtained	Date
Purchase dealer	Alpha(60)
Cost	Numeric
Date collected	Date
Collector	Alpha(40)
Collection location	Alpha(60)
Collection country	Alpha(50)
Collection details	Alpha(255)	collected in sand at 1m, etc.
Picture	Picture field

 

 

My synonyms database
Synonym no.	Numeric	allocated incrementally by the system
Shell record no.	Numeric	link to master record database
Synonym name	Alpha(25)
Synonym author	Alpha(50)
Synonym year	Numeric
Synonym type	Alpha(25)	usually synonym; flexibility for other name types

 

 

My picture database
Picture no.	Numeric	allocated by the system
Shell record no.	Numeric	link to master database
Collection specimen no.	Numeric	link to collection database
Directory	Alpha(30)
Folder	Alpha(50)
Filename	Alpha(90)
Format	Alpha(15)	jpg, etc.
Picture height	Numeric
Picture width	Numeric
Collection location info	Alpha(200)
Attribution	Alpha(80)	any author rights

 

You may also find useful the article by Dr Gary Rosenberg on organizing a collection on the Conchologists of America website.