Differences

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

--- projects:stopstogo:stopstogo:dbtables [2015/06/20 21: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 18: / Line 18: @@
 ====== Object Structures ======
-Tool to make ER database diagrams: http://highered.mcgraw-hill.com/sites/0072942207/student_view0/e_r_assistant.html
+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>

Dan's Wiki

User Tools

Site Tools

Differences

Page Tools