by Fred Hutcheson C5659
Modellers today use a variety of tools, not just knives, clamps and brushes, but increasingly computers and other electronic aids. One of the areas that is frequently overlooked is the use of databases to assist in organizing the information we use in the hobby. Specialist software has been developed specifically for modellers (see here). As the author points out, the database does not need to be only in electronic form, but can exist in ‘hard copy’ as well.
Note on the author: Fred Hutcheson is the person responsible for the IPMS Canada document RT Index that can be found and downloaded here. Fred, previously of Newfoundland, today resides in Shakespeare, Ontario.
In modelling, I suggest there are only two areas where databases are really useful. One is the ‘kit/build’ database; the other is a reference/ bibliographic database. I’m using the term ‘database’ in the widest meaning. A ‘database’ can have any number of ‘tables’ ; a ‘table’ may be a list (index) of articles in one publication, with another similar table for a different publication. Or one table can be aircraft kits, while another table is artillery kits.
By the way, databases don’t have to be in a computer. A book/binder of magazine indexes is a database, but it has some limitations for searching when compared to the computer version.
This leads to the first and biggest question…
What do you want your system to do?
Database design is a large and complex field. There are university courses in database design (and associated matters), so we won’t get very far into the details. We can build a very complex and inclusive database system, but if the data entry requirements are too steep, especially for the perceived benefits, then the system is a failure. The database design has to provide what you want, but not take up too much time. The database is a “tool” to help you with your modelling; it should not be the reason you model.
As mentioned above, we’ll discuss only two types: the simple ‘list’ database, and the ‘relational’ database.
List Databases
‘List’ databases are quite straight-forward and can be just a simple ‘list’ (or a group of ‘lists’). These are usually one ‘table’: columns are the ‘fields’ (or ‘variables’ (named in the top row)), and each row after the first is a ‘record’. Examples are: the RT Index, most publication indexes, or a bibliographic reference listing.
‘List’ databases can be handled by ‘spreadsheet’ programs (or in a big stretch, by ‘tables’ in word processing programs). Problems may occur when trying to locate a group of records (relating to the question) in the larger tables. You can search, and then search again etc, or you can sort the records to put all of the relevant records together.
For Indexes and lists provided in the ‘.pdf’ format, the Adobe® Acrobat Reader includes a ‘Find’ feature to search the file. (It works on the RT Index listings by the way.)
A Bibliographic Database
In the RT Index, there is one record for each article/item that has been published in the magazine (other than reports/comments by National Executive members), and it now includes articles with data included in beaveRTales. The ‘fields’ include: volume/number (date) of the issue, category (for example – aircraft), subject (for example – ‘Starfighter’), manufacturer, proper designation, type of item (for example – ‘a’ for an article), country, and ‘comments’.
I use a proper computer DBMS (data base management system) for the RT Index because: 1) I have one available, 2) it can keep ‘indexes’ based on identified fields up-to-date and then present the data ‘sorted’ according to the ‘active’ index, and 3) it has good facilities for entering the data and then formatting the reports.
I have defined the RT Index report formats in the DBMS and, when a report is ‘run’, all of the (acceptable) records in the table are included in the report (in the order given by the ‘active’ index). This is then ‘printed’ to a pseudo-printer driver that outputs a “.pdf” file.
Relational Databases
The ‘list’ design begins to have problems when the number of columns included get very large, especially as you add ‘fields’ for data that is not needed for every record. (An example is including columns/fields for five after-market items on each model record.) This usually leads to “sparse” data (or a lot of cells with nothing in them). This is less of a problem now (in 2013) with increasing amounts of memory available on personal computers than it was twenty (or thirty) years ago. Problems, such as misspellings and editing time, also arise when (what should be) the same data is included on many records.
The ‘relational model’ states that a data item should be entered only once in the database. The relational database is made up of at least two tables (or otherwise it would be a ‘list’ database).
For example, you may want your ‘reference article database’ to include, along with data on the subject, the identity of the author of each article (with contact information), and the detailed subscription information (address, web-site, e-mail etc) for each publication. Here we need (at least) three related tables. One table is the main article table (similar to the ‘list’ table discussed above). But, the main table will include two ‘relation’ fields; one which points to a record in the ‘author’ table, and another pointing to a record in the ‘publication’ table. Since the same author may write many articles, the ‘relational model’ says that the details about the author should be in a separate table. The ‘author’ table would have fields for the full name, the e-mail (and postal) address, phone numbers, and possibly forum ‘handles’ (and other data if desired (which may require more tables)). Similarly, there needs to be a table for the ‘publication’ data. Typically, the ‘relation’ field values in the main table would be the ‘record number’ (one of the ‘fields’ in the table) of the appropriate data record in the ‘related’ table. All of the tables require a ‘key’ field (which is indexed and the value has to be unique).
Relational DBMSs include the ability to index fields (or combinations of fields) to make the file appear ‘sorted’ according to the active index. These programs can select records based on identified criteria and then produce a report showing only the selected records. The data in the ‘related’ tables can be included with the data from the main table in these selections.
Relational databases can have subsidiary (‘child’) tables to restrict input in specified fields, or to provide long descriptions for short ‘codes’ that are used in the tables.
‘Relational’ databases can only be handled by a proper DBMS, or by a program system. Some times, the main DBMS can include the basic ‘manager’ part of itself with the defined tables, relations, input forms, indexes, and report formats to create a ‘packaged’ system that can be sold/distributed and used without the buyer/user having the full DBMS.
In my opinion, a proper ‘relational’ database is required for any useful kit/build database.
Different tables are needed for the various types of subjects modelled. Although, if you only build, say 1:48 scale F4 Phantom IIs (and similar aircraft), you don’t need tables suitable for armour ‘targets’, civilian cars and soft-skins, figures, and marine/naval subjects. If you build scenes and dioramas, then you may want to ‘relate’ the records for the various items in the scene. After-market parts should be in a separate table ‘related’ to the main kit record, since you could use from 0 to any number of parts in a complex build. Details on where and when a model was entered in a contest and the results would be another possible ‘related’ (‘child’) table to the main kit table. Another ‘child’ table in the ‘build’ database could be the specific references used for each model.
Decals sets are a different problem and require special type of ‘table relationship’ since many decal sets can be used on multiple models.
If you do a lot of ‘kit-bashing’, where you merge two (or more) different kits to create one model, then we would have to rethink our main table. The ‘main’ table would be based on the completed model. A ‘child’ table would identify the kit, or kits, used in the build, along with the tables for after-market parts, references, and contests mentioned above.
What should be recorded in the database, and how the various tables are related will be dependent on what you, the modeller, consider important. Also, we need to consider how the data is organized within the tables. The simplest way to add records is to have the system automatically generate the next unique record number. However, that means you have to include fields to allow you to sort/index the records your way.
A kit/build database
My Model database is a ‘relational’ database. I have done things in certain ways, or have not implemented possibilities, due to the history of my database, and my preferences. I started my database in 1984/85 on an early IBM® (8088) PC. I first used a ‘spreadsheet’ program, but switched to a DBMS (dBASE III and later dBASE IV) when the program became available in 1985. The ‘database’ was converted to another DBMS (Microsoft Access) in 2003.
My Model database has four ‘primary’ tables (Planes, Guns (tanks and artillery), Trucks (transport vehicles, which may be tracked & armoured, as well as civilian vehicles), and Ships (including airships & spaceships)). There is a ‘child’ table of missiles (related into the Plane, Gun, and Ship tables) (only for large guided missiles). There is a table of ‘diorama kits’ (Diokits) with records for groups of figures, sets of buildings, and base covers. The other ‘child’ file is for ‘dioramas’; data in this file is printed below the information on the main item (from Plane, Gun, Ship, or Truck).
Each table has a ‘free-text’ field (usually called ‘Notes’) where I enter a short history of the item modelled, including preceding & succeeding types, any personal connections, build comments, and other models of the type in the Collection.
I have also ‘coded’ data about the ‘era’ and scale of the model into the “model number” (which is the main ‘key’ in each table). Thus, a WW2 airplane about 1:72 scale in plastic has a model number between 0001 and 0199. Other eras and scales (and media) get numbers in different ranges. I still keep a paper list of acquisitions with assigned model numbers !
My Model database currently has 396 Plane records (this includes small-scale units associated with ship models), 79 Truck records, 94 Ship records, 83 Gun records, 10 missile records (mostly submarine-launched ICBMs), 26 diorama records, and 21 diorama kit records.
I have created several ‘list’ reports for each main file. I have also created ‘page’ reports (where each record goes on a separate page). These ‘page’ reports are used to format the data on the model to accompany contest-type displays.
I consider the inclusion of pictures (and scans) in the database system as a detriment. The size of the file (with pictures etc) would slow the system down noticeably. (There may be ways to ‘link’, within the database, to external images, without adding them to the main database file. This is more complex, but it would largely get around the size problem.) I keep the model pictures, reference images, and scans of boxes and plans, in another set of folders. This significantly reduces the size to the Model database. The Access file for my Model database is 2,516 K bytes. The folders with the pictures and scans are 4.89 G bytes and the archive is very incomplete. Most of the pictures are only 2M pixels in size so each image file is about 650 K bytes.
I write information, such as construction milestone dates, on the plans, as well as the actual paints used for each colour. The construction plans, especially the item history and the marking diagrams, are scanned after the model is complete. Construction ‘milestones’ and detailed colour data are not included in the computer database. I also use my ‘Notes’ (unstructured text) field to indicate after-market parts and decals used in the model. The box/covers and instructions for the after-market parts are scanned and included with the rest of the model imagery. Decals for one model (mostly for airliners) are treated as an after-market item.
I have not properly set up ways to document decals that can be, or have been, used on multiple models.
I also have some printed cards suitable for creating bases (for 1:72 aircraft). These are listed in the “Diokits” table, but they are not assigned to a specific model, since no model is fixed to any of these bases. Many of the ‘full-hull’ ship models have wooden bases under the (usually included) support brackets, but these are only mentioned in the ‘Notes’ field. Most of the ‘waterline’ ships have not been attached to bases (to date), although I have (undocumented) painted fibreboard pieces that they can sit on. I did build two ‘dioramas’ using 1:700 ships, vehicles, and buildings (but these were destroyed before the move to Ontario; the plastic items were retained).
