Database

Here are the tables for Where's The Party At; what data am I missing? Take the users and user_configurations tables to be given (they are given by the ModelSecurity generator).

We'll need a invites table for private parties. Lame.

CREATE TABLE parties (
    id SERIAL PRIMARY KEY,
    host_id INTEGER REFERENCES users(id) NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    created_on TIMESTAMP NOT NULL,
    updated_on TIMESTAMP NOT NULL,
    start TIMESTAMP NOT NULL,
    location_id INTEGER REFERENCES locations(id) NOT NULL,
    private BOOL NOT NULL DEFAULT 'false', -- invite only?
    self_announced BOOL NOT NULL DEFAULT 'true', -- to announce someone else's party
    cost FLOAT NOT NULL DEFAULT 0.0
);

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    street VARCHAR(255), -- Street name and number on that st.
    apt VARCHAR(20),
    city VARCHAR(255) NOT NULL,
    state CHAR(2) NOT NULL, -- The two letter state ID
    -- We're stuck in the USA.
    directions TEXT -- any directions the host thinks are useful (e.g. "bang loudly")
);

CREATE TABLE rsvps (
    id SERIAL PRIMARY KEY,
    party_id INTEGER REFERENCES parties (id) NOT NULL,
    name VARCHAR(255), -- The name of the person who RSVP'ed, or
    user_id INTEGER REFERENCES users (id), -- ... the ID of the user who RSVP'ed
    attendence VARCHAR(5) NOT NULL, -- one-of 'yes' 'no' 'maybe'
    CONSTRAINT attendence_constraint CHECK (attendence IN ('yes','no','maybe')),
    CONSTRAINT name_or_user_id CHECK (name IS NOT NULL or user_id IS NOT NULL)
);

CREATE TABLE users_to_locations (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) NOT NULL,
    location_id INTEGER REFERENCES locations(id) NOT NULL
);

CREATE TABLE votes (
    id SERIAL PRIMARY KEY,
    party_id INTEGER REFERENCES parties (id) NOT NULL,
    user_id INTEGER REFERENCES users(id) NOT NULL,
    overall BOOL,
    food BOOL,
    drink BOOL,
    music BOOL,
    atmosphere BOOL,
    value BOOL -- Was it worth the money or trip?
);

CREATE TABLE trackbacks (
    id SERIAL PRIMARY KEY,
    party_id INTEGER REFERENCES parties (id) NOT NULL,
    title TEXT, -- The title of the entry
    excerpt TEXT, -- An excerpt of the entry
    url TEXT NOT NULL, -- The permalink for the entry
    blog_name TEXT, -- The name of the weblog
    pinged_on TIMESTAMP NOT NULL
);

CREATE VIEW users_locations_counts AS
 SELECT l.id AS location_id, p.host_id AS user_id, COUNT(p.id)
 FROM parties p
 JOIN users_to_locations utl ON p.host_id = utl.user_id AND p.location_id = utl.location_id
 JOIN locations l ON l.id = p.location_id
 WHERE  p.self_announced
 GROUP BY p.host_id, l.id;