From 3d0f989cee9d7c426c759588539dd06c14fa70ea Mon Sep 17 00:00:00 2001
From: Ben Burwell <ben@benburwell.com>
Date: Sun, 2 Aug 2015 17:31:36 -0400
Subject: let's get v1 going here

---
 db/schema.sql | 36 ++++++++++++++++++++++++++++++++++++
 1 file changed, 36 insertions(+)
 create mode 100644 db/schema.sql

(limited to 'db/schema.sql')

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);
-- 
cgit v1.2.3