simplified the damn schema

1 files changed, 23 insertions, 53 deletions

diff --git a/src/lib/db/prosodyschema.sql b/src/lib/db/prosodyschema.sql
index 09dabc2..26818f3 100644
--- a/src/lib/db/prosodyschema.sql
+++ b/src/lib/db/prosodyschema.sql
@@ -57,22 +57,6 @@ CREATE TABLE IF NOT EXISTS words_wrhymes(
 );
 
 -- break up syllables
-CREATE TABLE IF NOT EXISTS syllables(
-    id INTEGER PRIMARY KEY AUTOINCREMENT,
-    lang TEXT NOT NULL,
-    ipa TEXT NOT NULL,
-    long INTEGER NOT NULL,
-    text TEXT NOT NULL,
-    onset TEXT NOT NULL,
-    medial TEXT NOT NULL,
-    nucleus TEXT NOT NULL,
-    coda TEXT NOT NULL,
-    rhyme TEXT NOT NULL,
-    notes TEXT,
-    FOREIGN KEY (lang) REFERENCES languages(iso6392),
-    CONSTRAINT syllable_unique UNIQUE (text, ipa, lang)
-);
-
 CREATE TABLE IF NOT EXISTS tones(
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     ipa TEXT NOT NULL,
@@ -116,45 +100,31 @@ CREATE TABLE IF NOT EXISTS rhymes(
     lang TEXT NOT NULL,
     CONSTRAINT onsets_unique UNIQUE (ipa, text, lang)
 );
+CREATE TABLE IF NOT EXISTS syllables(
+    id INTEGER PRIMARY KEY AUTOINCREMENT,
+    lang TEXT NOT NULL,
+    ipa TEXT NOT NULL,
+    long INTEGER NOT NULL,
+    text TEXT NOT NULL,
+    onset INTEGER  NOT NULL,
+    medial INTEGER  NOT NULL,
+    nucleus INTEGER  NOT NULL,
+    coda INTEGER  NOT NULL,
+    rhyme INTEGER  NOT NULL,
+    tone INTEGER NOT NULL,
+    notes TEXT,
+    FOREIGN KEY (lang) REFERENCES languages(iso6392),
+    FOREIGN KEY (onset) REFERENCES onsets(id),
+    FOREIGN KEY (medial) REFERENCES medials(id),
+    FOREIGN KEY (nucleus) REFERENCES nucleus(id),
+    FOREIGN KEY (coda) REFERENCES codas(id),
+    FOREIGN KEY (rhyme) REFERENCES rhymes(id),
+    FOREIGN KEY (tone) REFERENCES tones(id),
+    CONSTRAINT syllable_unique UNIQUE (text, ipa, lang)
+);
 
--- join tables
 
-CREATE TABLE IF NOT EXISTS tones_syllables(
-    syl_id INTEGER NOT NULL,
-    tone_id INTEGER NOT NULL,
-    FOREIGN KEY (syl_id) REFERENCES syllables(id),
-    FOREIGN KEY (tone_id) REFERENCES tones(id)
-);
-CREATE TABLE IF NOT EXISTS onsets_syllables(
-    syl_id INTEGER NOT NULL,
-    onset_id INTEGER NOT NULL,
-    FOREIGN KEY (syl_id) REFERENCES syllables(id),
-    FOREIGN KEY (onset_id) REFERENCES onsets(id)
-);
-CREATE TABLE IF NOT EXISTS medials_syllables(
-    syl_id INTEGER NOT NULL,
-    medial_id INTEGER NOT NULL,
-    FOREIGN KEY (syl_id) REFERENCES syllables(id),
-    FOREIGN KEY (medial_id) REFERENCES medials(id)
-);
-CREATE TABLE IF NOT EXISTS nucleus_syllables(
-    syl_id INTEGER NOT NULL,
-    nucleus_id INTEGER NOT NULL,
-    FOREIGN KEY (syl_id) REFERENCES syllables(id),
-    FOREIGN KEY (nucleus_id) REFERENCES nucleus(id)
-);
-CREATE TABLE IF NOT EXISTS codas_syllables(
-    syl_id INTEGER NOT NULL,
-    coda_id INTEGER NOT NULL,
-    FOREIGN KEY (syl_id) REFERENCES syllables(id),
-    FOREIGN KEY (coda_id) REFERENCES codas(id)
-);
-CREATE TABLE IF NOT EXISTS rhymes_syllables(
-    syl_id INTEGER NOT NULL,
-    rhyme_id INTEGER NOT NULL,
-    FOREIGN KEY (syl_id) REFERENCES syllables(id),
-    FOREIGN KEY (rhyme_id) REFERENCES rhymes(id)
-);
+-- join tables
 
 CREATE TABLE IF NOT EXISTS syllables_words(
     syl_id INTEGER NOT NULL,