CarLibrary.org - Databases

July 19, 2016

Database Example for Archives/Collections

Other CarLibrary.org webpages have described several approaches to organizing data: a spreadsheet such as Microsoft's Excel or a traditional database.  This webpage has experiences with the Microsoft Access 2000 database program used to create two trial databases, using Frazer Nash Car Club members data (redacted) and that of Frazer Nash cars.

Frazer Nash and Car Club Registry Database

One database, "Car Club" (with some real and mostly filler data) or "FrazerNash" (real Club data, for Club members only), was designed to show all car club member and all owners, over time, of the prewar and postwar Frazer Nash cars.  The data is contained in separate tables, with the relationships shown below by lines linking similar data elements in the tables.

One goal/feature of a relational database is to avoid duplication of data.  The table structure above does not completely meet this goal, but could be further improved.  

The "FN-Cars History" table above is a "junction table" which forms a "many-to-many" relationship between club members/owners and the Frazer Nash cars.  Any person can be shown to have owned many cars over time and any car can be queried to show its current and previous owners.  That is, after all the data is entered!

In the current Access database, not all these tables are used.  For example, the "CountyStates" table has all counties in England, states in the US and districts in Australia and Canada, intended to be a "lookup" for better uniformity and efficiency, but this function was not implemented. 

Currently, either of databases can produce on screen or printed reports to show all the Club members and their cars or all the Frazer Nash cars, with their owners (which includes Club members) over time.  The data currently available produces good results, but the research and data-entry stage needed for all cars and owners is a large task for dedicated Club members.  This database can be expanded to perform all traditional car club tasks - member information, event planning, mail lists, etc.

This is the opening screen of the Frazer Nash Car Club database:

If "View Cars and Owners" is selected from this menu screen, this traditional database form shows up:

The data elements can be updated or added in this form.  A search for a particular record can be added.  Much more interesting is the menu choice "View/print List of Cars and All Owners Over Time" which gives this table that can be viewed or printed:

Improving this database by adding queries, forms and reports to the main menu - or sub-menus - could continue.  However, both of these databases should be viewed as "roadmaps" for thought and future work - by others!

Car Collections Database

The same database techniques were adapted to organizing the inventory and basic data on individual autos in a single car collection, foreseeing a need to record basic data over several collections or museums.

As above, this database can be searched for a make or model of car over all the collections and provide a history, both on-screen and printed, of any car showing previous owners (which may also be a "collection").

Further Development

These Access databases were created with only moderate database experience and are intended to only be a roadmap or suggestion for a club, organization or developer to meet a need for better preservation of automotive history.  However, the real value of any database is the actual data.  By providing a structured - and perhaps collaborative - method to record this data, research should be encouraged and history better preserved.

Future work on this should consider:

  1. Discussions with car club/make registrars to understand their requirements.

  2. Improving the database user interface, structure, queries and reports.

  3. Transitioning the databases to an open-source database program, such as LibreOffice Base or My SQL.

  4. Providing a web (Internet) interface to the database for multi-user search or data updates.

The Sample Databases

These Access databases, last updated in 2012, can sent from the "cloud" upon request.  The Table of Contents has a link to Collections Management Systems, which are DBMS customized for collection and museum management. 

There was custom Access software designed to be a CMS - the "freeware" system, Tabularium, developed by David Roberts and the Government of New South Wales, Australia.  This template can be provided but requires Access 2000 to function.

Email me with any questions or comments: Bob Schmitt, rgschmitt@gmail.com