1 files changed, 65 insertions, 0 deletions
diff --git a/design/database.sql b/design/database.sql
index e69de29..c056c1c 100644
--- a/design/database.sql
+++ b/design/database.sql
@@ -0,0 +1,65 @@
+-- Foursquare Community Site
+-- 
+-- Copyright (C) 2011 Foursquare Church.
+-- 
+-- Developers: Jesse Morgan <jmorgan@foursquarestaff.com>
+
+
+-- The following cleans up existing data
+
+DROP DATABASE IF EXISTS p4scommunity;
+DROP USER p4scommunity@localhost;
+
+-- The following creates a database and user
+
+CREATE DATABASE p4scommunity;
+
+CREATE USER p4scommunity@localhost IDENTIFIED BY 'password';
+
+GRANT ALL ON p4scommunity.* TO p4scommunity@localhost;
+
+USE p4scommunity;
+
+-- The following creates the table structure
+
+CREATE TABLE category (
+    id          INTEGER     UNSIGNED NOT NULL AUTO_INCREMENT,
+    name        VARCHAR(30) NOT NULL,
+
+    PRIMARY KEY(id)
+);
+
+CREATE TABLE source (
+    id          INTEGER     UNSIGNED NOT NULL AUTO_INCREMENT,
+    name        VARCHAR(60) NOT NULL,
+
+    PRIMARY KEY(id),
+    UNIQUE  KEY(name)
+);
+
+CREATE TABLE post (
+    id          INTEGER     UNSIGNED NOT NULL AUTO_INCREMENT,
+    name        VARCHAR(60) NOT NULL,
+    category_id INTEGER     UNSIGNED NOT NULL,
+    created     DATETIME    NOT NULL,
+    description TEXT        NOT NULL,
+    
+    email       VARCHAR(255) NOT NULL,
+    secretid    VARCHAR(32)  NOT NULL,
+
+    source_id   INTEGER     UNSIGNED NOT NULL,
+
+    PRIMARY KEY(id),
+    UNIQUE  KEY(secretid)
+);
+
+
+-- The following creates some sample data
+INSERT INTO category (name) VALUES 
+    ('Jobs'), 
+    ('Housing'),
+    ('Events'),
+    ('For Sale'),
+    ('Needs');
+
+INSERT INTO source (name) VALUES ('Foursquare Church');