Database Structure

The Item Database

The Inventory Database

Edit Tables

Technical Term Definitions

Reference Summary

These two segments are related in a "one-to-many" relationship. This means that for any item record, there could be zero, one, or more inventory records related to that item.

Consider the example where a collector owns three Micro-Trains 50' Standard Box Cars with a Manufacturer Model Number of 75070, identical markings, and that are otherwise indistinguishable as items. The user would enter this kind of item information once as an item record. Then, the user would enter the information for each physical car into three inventory records related to the common item record. This information could include different purchase dates, conditions, storage locations or other things that might differ across the three different physical occurrences of the same item. In this way, our system treats these three almost identical cars as a single occurrence of item and three occurrences of the inventory information.

The item record describes the item in its entirety and refers to items that you own or want to own. The inventory section allows you to put specific information about an item such as its condition, location, value, cost, and sale information. If you own more than one of each item, the inventory section allows you to keep track of each individual piece without having to enter all common information more than once.

You could have an item record without an inventory record, such as an item you have never acquired and have not yet chosen to put on your wish list. But you could never have an inventory record without an item record describing what it is that is being inventoried.

The design of the system is such that any physical items that are identical in every field contained in the item segment should have one item record and as many related inventory records as there are occurrences of that item (even if this inventory information is also identical for each piece.) The only slight exception to this is for "wish list" items. For these, an inventory record is added to the item record and the wish list field is clicked on to indicate a wish list item.

One final concept associated with our database structure is that of the record key. The system assigns a unique sequential item number to each record so that each item record can be uniquely identified or (named) internally. Users have no control of this item number assignment process, but they can use it to find and specify an exact record that could in every other way be identical to another item record. Within this application, the item key is an 8-digit number (so we can theoretically accommodate collections with up to 100 million different items!) For every inventory record associated with an item, an additional sequence number is added to the end of the item number to give a unique key to each inventory record associated with an item.

The item key is a system assigned control field.

Item Key

Three table edited classification fields allow for increasing levels of detail in classifying your items. A fourth free-format field allows additional descriptive information.

All edit tables in TrackMyTrains.com can be completely customized to the needs of the individual subscriber. This section explains more about the edit tables and how they should be maintained.

Creating and maintaining edit tables is a relatively easy matter. Certain of these are partially loaded when you start your subscription to get you started on the right track. Also, the demonstration databases show some uses. While you can change these anyway you choose, we caution you to stay to the spirit of the design, particularly on the organizational fields and those that could relate to outside entities.

To add an entry to a table, simply click on "Add/Delete". This will display all values and a place will appear at the bottom to specify the new entry. Key in the value you want, click "OK" and you are done. If you choose not to add an entry, or see that the one you want is already there, click instead on the desired entry and then click "OK".

If you want to delete an entry, first start as if you were going to add one. Then select the entry, highlight it, and click delete. Note that to maintain data integrity, the system will not let you delete any value that occurs in any record. First, you should find all records containing the value you wish to delete using a select statement. Then change the field value to some value you choose or blank it out by clicking the blank row of the edit table. When the value has been removed from all data records, you can then delete it from the edit table.

The edit tables associated with certain fields are "context sensitive." This means that the table used for editing the field depends upon the value of another data element. One example is the Item Type field edit table. It is context sensitive to the value of the Item Category and there is a different Item Type edit table for each value in the Item Category table. Other relationships are listed below. Note that you cannot specify the value of a dependent field until you have done so for the field its edit table depends on. Also, if you change the value of a field that provides the context for a dependent field, the dependent field will be blanked-out as there is no context for selecting the proper value.

Multiple field edit tables control the values in certain related fields. This means that when you specify a particular table entry for one field, it forces a value in the related field. The Item Railroad Abbreviation and the Item Railroad Name are coupled in this manner. By having two separate data fields, it is easier to select records and choose data to appear on reports than it would be if both parts were kept in the same field. The table selections are each organized alphabetically. So for example, both SSW and Cotton Belt appear alphabetically in their respective look up tables, yet you need only pick one field to have the other properly filled in.

Edit Table: An edit table is a list of values or contents that may be contained in a field. Rather than keying in the value for a table-edited field, its contents are established by selecting a value from its edit table.

Flag: A flag is a data field that can represent two values. Typically these values are referred to as "Yes" or "No", "True" or "False", or "On" or "Off". In this applications, flags are displayed as check boxes with the check or dot representing the value of 'Y' (Yes) or interchangeably, in the record selection features, 'T' (True).

Record Key: A record key is a string of characters that uniquely identifies a particular database record or segment.

Segment: A segment is a group of data elements that constitute a database record. Segment is sometimes used interchangeably with the word "record."

Table Edited: A field is said to be table edited if it is only permitted to contain values that are specified as valid. The list of valid values for a data field is called its edit table.

Tagging Records: Tagging records refers to the technique of entering a string of characters in one or more of the fields of a record so that the record can be retrieved along with other records containing the same "tag" by system reports and inquiries. The Notes fields of the Item and Inventory segments are good places for entering tags that can be used to associate multiple records with a common identifying theme. One can retrieve these records as a group by specifying a record selection statement in which the tag field "contains" the tag value.