First, read “A free, open song contest idea.”
The big idea is that this songwriting contest will mostly run itself, on a monthly loop. A manager's role is only to contact companies to ask if they want to donate prizes for the winners, in return for the exposure of having the musicians pore over the list of prizes, with links back to the company's site for more info. (The power of drool.) A little admin work to enter the prize info, make sure the winner gets it, and oversee the system to make sure it's not being gamed in some surprising way, and voilá! Free song contest.
A (musician) person creates a song, with a title, artist, lyrics, and genre. They probably upload the audio to SoundCloud.
Then any (normal) person can create a vote for that song anonymously, (they can hear the song but don't know who it is until after they vote), which is a numeric rating with a comment and date.
The site manager is contacting companies to contribute prizes to the contest. When one agrees, the manager creates a company, tied to a person_id, and at least one prize, with a name and description.
The musician who has added a song to the contest can browse through the prizes and add a wish for their song to win any number of available prizes for that contest.
At the end of the month, all votes are tallied to create a winner, which is really just a cached summary of that month's activity.
Then the manager goes through the winning songs to assign the prizes (based on the wishes, when possible), by updating that prize's song_id to match the winning song.
|/person/2||person info||.||update person||.|
|/person/2/songs||songs by this person||.||.||.|
|/person/2/votes||votes by this person||.||.||.|
|/genre||list all genres||code to add new genre||.||.|
|/genre/jazz||genre info||.||update genre||delete genre|
|/genre/jazz/songs||songs in this genre||.||.||.|
|/genre/jazz/winners||winning songs in this genre||.||.||.|
|/song||list all songs||person_id, artist, title, soundcloud_url, lyrics, genre to create new song||.||.|
|/song/random||a random song, used for voting. redirects to a random temp-ID anonymizing of song||.||.||.|
|/song/hf71oza9fea7||anonymous representation of song with one-time-use random ID. ID must be used for voting to prove vote was blind.||.||.||.|
|/song/2||song info||.||update song||delete song|
|/song/2/votes||votes for song||.||.||.|
|/song/2/wishes||list of prizes wished for by this song||.||.||.|
|/vote/hf71oza9fea7||.||person_id, vote number, comment to create vote||.||.|
|/vote/2||vote info||.||update vote||delete vote|
|/company||list all companies||person_id, name, url to add new company||.||.|
|/company/2||company info and list of their prizes||.||update company||delete company|
|/prize||list all prizes||company_id, name, description to add new prize||.||.|
|/prize/available||list of available prizes||.||.||.|
|/prize/2||prize info||.||update prize||delete prize|
|/prize/2/wishes||list of songs that wished for this prize||.||.||.|
|/wish||.||song_id, prize_id to create wish||.||.|
|/wish/song.id/prize.id||.||.||.||remove this wish|
|/winner||list of winners||.||.||.|
|/winner/2||details about this winner||.||.||.|
BEGIN; CREATE SCHEMA songtest; SET search_path = songtest; CREATE TABLE genres ( code varchar(16) primary key ); CREATE TABLE songs ( id serial primary key, person_id integer not null, lang char(2) not null default 'en', artist text, title text, soundcloud_url text, lyrics text, genre varchar(16) REFERENCES genres, created_at timestamp with time zone not null default CURRENT_TIMESTAMP ); CREATE INDEX songs_person ON songs(person_id); CREATE INDEX songs_genre ON songs(genre); CREATE TABLE votes ( id serial primary key, person_id integer not null, song_id integer not null REFERENCES songs, vote integer, comment text, created_at timestamp with time zone not null default CURRENT_TIMESTAMP, UNIQUE (person_id, song_id) ); CREATE INDEX votes_person ON votes(person_id); CREATE INDEX votes_song ON votes(song_id); CREATE TABLE companies ( id serial primary key, person_id integer not null, name text, url text ); CREATE TABLE prizes ( id serial primary key, company_id integer not null REFERENCES companies, name text, description text, -- English created_at timestamp with time zone not null default CURRENT_TIMESTAMP, song_id integer REFERENCES songs -- NULL until claimed ); CREATE INDEX prizes_company ON prizes(company_id); CREATE INDEX prizes_song ON prizes(song_id); CREATE TABLE wishes ( song_id integer not null REFERENCES songs, prize_id integer not null REFERENCES prizes, PRIMARY KEY (song_id, prize_id) ); CREATE TABLE winners ( -- cache/summary id serial primary key, month char(7) not null, genre varchar(16) not null REFERENCES genres, song_id integer not null REFERENCES songs, votes integer not null, average numeric ); CREATE INDEX winner_month ON winners(month); CREATE INDEX winner_genre ON winners(genre); COMMIT;