aboutsummaryrefslogtreecommitdiff
path: root/db
diff options
context:
space:
mode:
authorBen Burwell <ben@benburwell.com>2015-08-02 17:31:36 -0400
committerBen Burwell <ben@benburwell.com>2015-08-02 17:31:36 -0400
commit3d0f989cee9d7c426c759588539dd06c14fa70ea (patch)
tree798fb7c7f10282be06f85814e20ba062a9d6f635 /db
parent2cde3ec173c57d24e6fb8a31700a7db4eb31564d (diff)
let's get v1 going here
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);