aboutsummaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
Diffstat (limited to 'db')
-rw-r--r--db/schema.sql36
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);