diff options
author | Ben Burwell <ben@benburwell.com> | 2015-08-02 17:33:25 -0400 |
---|---|---|
committer | Ben Burwell <ben@benburwell.com> | 2015-08-02 17:33:25 -0400 |
commit | 17470146704846137bf09723d403a5650208ba28 (patch) | |
tree | 798fb7c7f10282be06f85814e20ba062a9d6f635 /db/schema.sql | |
parent | 3788738f88b3aed5ced113aa4c455a0d25d8b84e (diff) | |
parent | 3d0f989cee9d7c426c759588539dd06c14fa70ea (diff) |
Merge pull request #2 from benburwell/mvp
Minimum Viable Product
Diffstat (limited to 'db/schema.sql')
-rw-r--r-- | db/schema.sql | 36 |
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); |