From 1061be2b09a29efd93d71ccafd9ff758e7a7681f Mon Sep 17 00:00:00 2001
From: Matthias Andreas Benkard <code@mail.matthias.benkard.de>
Date: Tue, 1 Mar 2011 22:15:45 +0100
Subject: Normalize the database schema.

---
 schema.sql | 31 +++++++++++--------------------
 1 file changed, 11 insertions(+), 20 deletions(-)

diff --git a/schema.sql b/schema.sql
index 6304790..914398c 100644
--- a/schema.sql
+++ b/schema.sql
@@ -24,25 +24,19 @@ CREATE TABLE users(
 
 CREATE TABLE passwords(
   "user"   INTEGER NOT NULL,
-  id       INTEGER NOT NULL,
   password VARCHAR NOT NULL,
-  PRIMARY KEY ("user", id),
   FOREIGN KEY ("user") REFERENCES users
 );  
 
 CREATE TABLE openids(
   "user" INTEGER NOT NULL,
-  id     INTEGER NOT NULL,
   openid VARCHAR NOT NULL,
-  PRIMARY KEY ("user", id),
   FOREIGN KEY ("user") REFERENCES users
 );  
 
 CREATE TABLE login_certificates(
   "user"      INTEGER NOT NULL,
-  id          INTEGER NOT NULL,
   certificate BYTEA NOT NULL,
-  PRIMARY KEY ("user", id),
   FOREIGN KEY ("user") REFERENCES users
 );
 
@@ -61,53 +55,50 @@ CREATE TABLE article_aliases(
 CREATE TABLE comments(
   id      SERIAL  NOT NULL,
   article INTEGER NOT NULL,
-  PRIMARY KEY (id, article),
+  PRIMARY KEY (id),
   FOREIGN KEY (article) REFERENCES articles
 );
 
 CREATE TABLE article_revisions(
+  id        SERIAL    NOT NULL,
   article   INTEGER   NOT NULL,
-  id        INTEGER   NOT NULL,
   date      TIMESTAMP DEFAULT now(),
   title     VARCHAR   NOT NULL,
   content   VARCHAR   NOT NULL,
   author    INTEGER,
   format    varchar   NOT NULL,
-  PRIMARY KEY (article, id),
+  PRIMARY KEY (id),
   FOREIGN KEY (article) REFERENCES articles,
   FOREIGN KEY (author)  REFERENCES users,
   CHECK (format IN ('html'))
 );
 
 CREATE TABLE article_revision_characteristics(
-  article        INTEGER NOT NULL,
   revision       INTEGER NOT NULL,
   characteristic VARCHAR NOT NULL,
   value          VARCHAR,
-  FOREIGN KEY (article, revision) REFERENCES article_revisions
+  FOREIGN KEY (revision) REFERENCES article_revisions
 );
 
 CREATE TABLE article_revision_parenthood(
-  article   INTEGER NOT NULL,
   parent_id INTEGER NOT NULL,
   child_id  INTEGER NOT NULL,
-  FOREIGN KEY (article, parent_id) REFERENCES article_revisions,
-  FOREIGN KEY (article, child_id)  REFERENCES article_revisions
+  FOREIGN KEY (parent_id) REFERENCES article_revisions,
+  FOREIGN KEY (child_id)  REFERENCES article_revisions
 );
 
 CREATE TABLE comment_revisions(
-  article          INTEGER   NOT NULL,
-  comment_id       INTEGER   NOT NULL,
-  id               INTEGER   NOT NULL,
+  id               SERIAL    NOT NULL,
+  comment          INTEGER   NOT NULL,
   date             TIMESTAMP DEFAULT now(),
   content          VARCHAR   NOT NULL,
   author           INTEGER,
   format           VARCHAR   NOT NULL,
   status           VARCHAR   NOT NULL,
   article_revision INTEGER,
-  PRIMARY KEY (article, comment_id, id),
-  FOREIGN KEY (article, comment_id) REFERENCES comments,
-  FOREIGN KEY (author)              REFERENCES users,
+  PRIMARY KEY (id),
+  FOREIGN KEY (comment) REFERENCES comments,
+  FOREIGN KEY (author)  REFERENCES users,
   CHECK (status IN ('pending', 'approved', 'trusted', 'deferred', 'spam',
                     'rejected')),
   CHECK (format IN ('text'))
-- 
cgit v1.2.3