====== StopsToGo Database Tables ======

^Table Name^Column^Spec^Notes^
^ORGAN_STOP^_ID|varchar(100)|Main ID, required field,\\ value suggest by [[https://developer.android.com/training/basics/data-storage/databases.html#DefineContract|Android (1)]]|
^:::|ROOT_ID|varchar(100)|Specifies the main root ID, if this is a synonym|
^:::|NAME|varchar(200)|Stop Name|
^:::|NAME_LANGUAGE|varchar(100)|Language of the stop name, e.g., German, Dutch|
^:::|SUBTYPE|varchar(100)|Sub-Type, such as "mixture" or "percussion"|

<code>
create table ORGAN_STOP (_ID varchar(100) PRIMARY KEY, 
ROOT_ID varchar(100),
NAME varchar(200),
NAME_LANGUAGE varchar(100),
SUBTYPE varchar(100))
</code>

====== Object Structures ======

Simpleton tool to make ER database diagrams: http://highered.mcgraw-hill.com/sites/0072942207/student_view0/e_r_assistant.html  - Decided not to use this because can't see a way to change the size of the Entity boxes.

Now using DBVisualizer, Free version. This lets you type SQL and modify the underlying SQLite DB, and it will draw the relationships. I added "Foreign Key" references to support this.
   * [[http://confluence.dbvis.com/display/HOME/Documentation+Home|DBVisualizer Docs]]

Current SQL to make the database tables:

<code>
create table MANUAL_TYPE (_ID integer primary key, NAME varchar(100));
create table COUNTRY (_ID integer primary key, NAME varchar(100), ABBREV_2 varchar(2) unique)
CREATE UNIQUE INDEX i1 ON COUNTRY(ABBREV_2);
create table LOCATION (_ID integer primary key, ORGANIZATION_NAME varchar(200), ADDRESS_1 Varchar(200), ADDRESS_2 varchar(200), CITY varchar(100), state varchar(2), COUNTRY_ABBREV_2 varchar(2), POSTAL_CODE varchar(20), FOREIGN KEY(COUNTRY_ABBREV_2) REFERENCES COUNTRY(ABBREV_2));
create table ORGAN (_ID INTEGER PRIMARY_KEY, NAME VARCHAR(200), OHS_DB_ID INTEGER, LOCATION_ID INTEGER, FOREIGN KEY(LOCATION_ID) REFERENCES LOCATION(_ID));
create table STOP (_ID integer primary key, NAME varchar(200), ROOT_ID integer, LANGUAGE_ID integer, STOP_TYPE_ID integer, DESCRIPTION varchar(4000), FOREIGN KEY(ROOT_ID) REFERENCES STOP(_ID));
create table MANUAL_FOR_ORGAN (_ID integer primary key, MANUAL_TYPE_ID integer, ORGAN_ID integer, FOREIGN KEY(MANUAL_TYPE_ID) REFERENCES MANUAL_TYPE(_ID), FOREIGN KEY(ORGAN_ID) REFERENCES ORGAN(_ID));
create table STOP_FOR_MANUAL(_ID integer primary key, MANUAL_ID integer, STOP_ID integer, PIPE_SIZE_ID integer, MINIMUM_NOTE varchar(5), MAXIMUM_NOTE varchar(5), FOREIGN KEY(STOP_ID) REFERENCES STOP(_ID), FOREIGN KEY(MANUAL_ID) REFERENCES MANUAL_FOR_ORGAN(_ID));
create table COUPLER_FOR_MANUAL (_ID integer primary key, CONTROLLER_MANUAL_ID integer, SOUNDING_MANUAL_ID integer,FOREIGN KEY(CONTROLLER_MANUAL_ID) REFERENCES MANUAL_FOR_ORGAN(_ID), FOREIGN KEY(SOUNDING_MANUAL_ID) REFERENCES MANUAL_FOR_ORGAN(_ID));
create table COUNTRY (_ID integer primary key, NAME varchar(100), ABBREV_2 varchar(2) unique)

</code>


  * Stop
    * _ID
    * Name
    * Root Stop ID (for Synonym stop names)
    * Language (German, Dutch, etc.)
    * Type (Reed, flue, etc.)
    * Description
  * Manual Type
    * _ID
    * Name (Pedal, Great, Echo, etc.)
  * Organ
    * _ID
    * Name
    * Location_ID
  * Location
    * _ID
    * ADDRESS_1
    * ADDRESS_2
    * CITY
    * STATE
    * COUNTRY
    * POSTAL_CODE
  * Manual For Organ
    * _ID
    * MANUAL_TYPE_ID
    * ORGAN_ID
  * Stop For Manual
    * _ID
    * MANUAL_ID
    * STOP_ID
    * PIPE_SIZE_ID
    * MINIMUM_NOTE (e.g., C1)
    * MAXIMUM_NOTE (e.g., C5)
<code>
create table STOP_FOR_MANUAL(_ID integer primary key, MANUAL_ID integer, STOP_ID integer, PIPE_SIZE_ID integer, MINIMUM_NOTE varchar(5), MAXIMUM_NOTE varchar(5))
</code>
  * Coupler For Manual
    * _ID
    * CONTROLLER_MANUAL_ID
    * SOUNDING_MANUAL_ID