1 files changed, 36 insertions, 0 deletions
diff --git a/db/schema.sql b/db/schema.sql
new file mode 100644
index 0000000..a7509da
--- /dev/null
+++ b/db/schema.sql
@@ -0,0 +1,36 @@
+-- Create the phrase table
+-- This will hold the phrases with an associated ID
+-- Phrases will be varchars of solfege syllables normalized as:
+--   a = do
+--   b = do+/re-
+--   c = re
+--   d = re+/mi-
+--   e = mi
+--   f = fa
+--   g = fa+/so-
+--   h = so
+--   i = so+/la-
+--   j = la
+--   k = la+/ti-
+--   l = ti
+CREATE TABLE IF NOT EXISTS phrases (
+  phrase_id BIGSERIAL PRIMARY KEY,
+  solfege VARCHAR(30) NOT NULL UNIQUE
+);
+
+-- Create a table that allows us to have a many-to-many
+-- relationship between phrases and songs (i.e., a phrase can be
+-- contained within multiple songs, and a song can have multiple phrases).
+CREATE TABLE IF NOT EXISTS phrase_song (
+  phrase_id BIGINT NOT NULL,
+  song_id BIGINT NOT NULL
+);
+
+CREATE TABLE IF NOT EXISTS songs (
+  song_id BIGSERIAL PRIMARY KEY,
+  title VARCHAR(255),
+  artist_name VARCHAR(255)
+);
+
+-- Now we need an index that will allow us to search more quickly for phrases
+CREATE UNIQUE INDEX phrases_solfege ON phrases (solfege);