Songtest

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.

status: UNSTARTED

Schema

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.

REST API

Database Schema

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;