RAILS uses at its heart a database to store all persistent data.
The preliminary database design focuses on entities related to rolling stock. Those entities exist in two categories, those related to modeling and those related to the railroads to be operated. The design work is being done using IBM InfoSphere Data Architect. The following ERD shows the preliminary database. The DDL to create the RAILS database can be found in GitHub.
Table AAR
| Attribute | Type | Definition | Comments |
|---|---|---|---|
| AAR_CODE | CHAR(5) | NOT NULL, Primary Key | |
| DEFINITION | VARCHAR(2048) |
Table COMPANY
A enterprise that builds, assembles and or sells model train components including but limited to scenery materials, track, rolling stock, buildings, and electronics.
| Attribute | Type | Definition | Comments |
|---|---|---|---|
| CO_ID | INTEGER | A unique number generated from a sequence to provide a key for this record. | NOT NULL, Primary Key |
| CO_TYPE | CHAR(1) | v, m, or b | |
| CO_NAME | VARCHAR(50) | ||
| CO_DESCRIPTION | VARCHAR(1000) | ||
| URL | VARCHAR(100) | ||
| PHONE | VARCHAR(15) | ||
| STREET | VARCHAR(50) | ||
| CITY | VARCHAR(25) | ||
| STATE | CHAR(2) | ||
| ZIP | VARCHAR(10) | ||
| COUNTRY | VARCHAR(20) |
Table DCC_ATTRIBUTE
| Attribute | Type | Definition | Comments |
|---|---|---|---|
| DCC_ATTR_ID | INTEGER | A unique number generated from a sequence to provide a key for this record. | NOT NULL, Primary Key |
| ATTR_NAME | CHAR(5) | ||
| ATTR_VALUE | CHAR(5) | ||
| RS_ID | INTEGER | Foreign Key |
Table IMAGES
| Attribute | Type | Definition | Comments |
|---|---|---|---|
| IMAGE_ID | INTEGER | A unique number generated from a sequence to provide a key for this record. | NOT NULL, Primary Key |
| TITLE | VARCHAR(25) | ||
| PIC | BLOB(4096) | INLINE LENGTH 92 |
Table INDUSTRY
| Attribute | Type | Definition | Comments |
|---|---|---|---|
| SHORT_NAME | VARCHAR(10) | An abbreviated form of identifying an entity conducting business. When that enterprise is an owner or lessees of rolling stock it is the “reporting marks”. | NOT NULL, Primary Key |
| FULL_NAME | VARCHAR(50) | ||
| IND_TYPE | VARCHAR(10) | NOT NULL | |
| FUNCTION | VARCHAR(250) | ||
| PRODUCT | VARCHAR(25) | ||
| RAW_MATERIAL | VARCHAR(25) | ||
| PRODUCTION_RATE | DOUBLE | ||
| PRODUCTION_UNIT | CHAR(5) | ||
| CONSUMPTION_RATE | DOUBLE | ||
| CONSUMPTION_UNIT | CHAR(5) |
Table MQTT_MSG
A communication using the ISO standard (ISO/IEC PRF 20922) publish-subscribe-based “lightweight” messaging protocol for use on top of the TCP/IP protocol.
| Attribute | Type | Definition | Comments |
|---|---|---|---|
| MSG_ID | INTEGER | A unique number generated from a sequence to provide a key for this record. | NOT NULL, Primary Key |
| TOPIC | VARCHAR(25) | A string or filter of a subject, purpose or matter to organize publications and subscriptions. Topic Strings are used to send publications to subscribers and topic filters are used to subscribe to topics. | |
| CONTENT | VARCHAR(50) | The substantive information contained within the MQTT message expressed in JavaScript Object Notation (JSON). | NOT NULL |
| ACTION | CHAR(1) | An indicator to whether this transaction is a publish or subscribe message | p or s |
| HANDLED_TIME | TIMESTAMP | The current time when this record has been processed by a RAILS component after the record was created. | |
| ACTION_TIME | TIMESTAMP | The current time when this record is created as a result of a publish or subscribe event. | Triggered on insert |
Table PROJECT
| Attribute | Type | Definition | Comments |
|---|---|---|---|
| PRJT_ID | INTEGER | A unique number generated from a sequence to provide a key for this record. | NOT NULL, Primary Key |
| PRJT_TITLE | VARCHAR(50) | ||
| PRJT_TYPE | VARCHAR(15) | ||
| PRJT_DESCRIPTION | VARCHAR(1000) | ||
| START_DATE | DATE | ||
| COMPLETED_DATE | DATE | ||
| PRJT_NOTES | VARCHAR(2000) | ||
| PRIORITY | VARCHAR(15) |
Table PROJECT_ITEM
| Attribute | Type | Definition | Comments |
|---|---|---|---|
| PRJT_ITEM_ID | INTEGER | A unique number generated from a sequence to provide a key for this record. | NOT NULL, Primary Key |
| ITEM_ID | INTEGER | NOT NULL | |
| PRJT_ID | INTEGER | NOT NULL | |
| QUANTITY | INTEGER | ||
| RS_ID | INTEGER | NOT NULL |
Table PURCHASE
| Attribute | Type | Definition | Comments |
|---|---|---|---|
| PURCHASE_ID | INTEGER | A unique number generated from a sequence to provide a key for this record. | NOT NULL, Primary Key |
| PURCHASE_DATE | DATE | ||
| VENDOR_ID | INTEGER | NOT NULL |
Table PURCHASEDITEM
| Attribute | Type | Definition | Comments |
|---|---|---|---|
| ITEM_ID | INTEGER | A unique number generated from a sequence to provide a key for this record. | NOT NULL, Primary Key |
| PURCHASE_ID | INTEGER | NOT NULL | |
| PART_NUMBER | VARCHAR(20) | NOT NULL | |
| DESCRIPTION | VARCHAR(50) | ||
| ITEM_COST | DECFLOAT(16) | ||
| UNIT_QTY | INTEGER | The number of things packaged in a single item acquired. In many cases this will be one for rolling stock. | Default of 1 |
| NOTES | VARCHAR(200) | ||
| MANUFACTURE_ID | INTEGER | NOT NULL | |
| CONSUMED | CHAR(1) |
Table ROLLINGSTOCK
Any vehicle that moves on a railway. It includes both powered and un-powered vehicles, for example locomotives, freight cars, tankers, coaches, and wagons.
| Attribute | Type | Definition | Comments |
|---|---|---|---|
| RS_ID | INTEGER | A unique number generated from a sequence to provide a key for this record. | NOT NULL, Primary Key |
| RS_TYPE_ID | VARCHAR(15) | NOT NULL | |
| IMAGE_ID | INTEGER | ||
| AAR_CODE | CHAR(5) | ||
| BUILT_BY | VARCHAR(10) | ||
| REPORTING_MARKS | VARCHAR(10) | The code of one to four letters used to identify the owners or lessees painted on RS. | |
| RS_NUMBER | VARCHAR(10) | ||
| RFID | VARCHAR(10) | The unique 10 digit number of the Radio Frequency Identification (RFID) tag when read. | |
| AVAILABILITY | CHAR(1) | The state of the RS as to whether it is able to be used and is at a dispatcher’s disposal for a given session or it is not. | ‘y’ RS is available, ‘n’ RS is not available |
| LOCATION_ID | VARCHAR(10) | ||
| LENGTH | DECIMAL(10 , 1) | ||
| CAPACITY | DECIMAL(10 , 1) | ||
| CAP_UNITS | CHAR(5) | ||
| MODEL_WEIGHT | DECIMAL(10 , 2) | ||
| LT_WEIGHT | INTEGER | ||
| LD_MAX_WEIGHT | INTEGER | ||
| BUILT_DATE | DATE | ||
| IN_SERVICE | DATE | ||
| HEIGHT | DECIMAL(10 , 1) | ||
| NOTES | VARCHAR(500) | ||
| LAST_MAINTENANCE | DATE | ||
| LAST_UPDATE | TIMESTAMP |
Table RS_TYPE
| Attribute | Type | Definition | Comments |
|---|---|---|---|
| RS_TYPE | VARCHAR(15) | NOT NULL, Primary Key | |
| CATEGORY | VARCHAR(15) | NOT NULL | |
| DESCRIPTION | VARCHAR(500) |
Table TURNOUT
| Attribute | Type | Definition | Comments |
|---|---|---|---|
| TURNOUT_ID | INTEGER | A unique number generated from a sequence to provide a key for this record. | NOT NULL, Primary Key |
| NAME | VARCHAR(50) | ||
| TURNOUT_TYPE | VARCHAR(10) | ||
| LOCATION | VARCHAR(50) | ||
| CNTRLR | VARCHAR(10) | ||
| COMMANDED_STATE | VARCHAR(10) | ||
| THROWN_SPEED | INTEGER | ||
| CLOSED_SPEED | INTEGER | ||
| KNOWN_STATE | VARCHAR(10) | ||
| IS_INVERTED | CHAR(1) | ||
| IS_LOCKED | CHAR(1) | ||
| LOCKED_MODE | VARCHAR(15) | ||
| LOCKED_BY | CHAR(5) | ||
| LAST_UPDT | TIMESTAMP |