Differences

This shows you the differences between two versions of the page.

--- projects:stopstogo:stopstogo:dbtables [2015/04/19 00:56]
dwheele
+++ projects:stopstogo:stopstogo:dbtables [2016/06/10 00:34] (current)
admin ↷ Page moved from stopstogo:stopstogo:dbtables to projects:stopstogo:stopstogo:dbtables
@@ Line 2: / Line 2: @@
 ^Table Name^Column^Spec^Notes^
-^ORGAN_STOP^ID|varchar(100)|Main ID, required field|
+^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,
+create table ORGAN_STOP (_ID varchar(100) PRIMARY KEY,
 ROOT_ID varchar(100),
 NAME varchar(200),
-NAME_LANGUAGE varchar(100))
+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

Dan's Wiki

User Tools

Site Tools

Differences

Page Tools