 
 
 --
--- Name: entries; Type: TABLE; Schema: atom; Owner: axelrod; Tablespace: 
+-- The entries relation
 --
 
 CREATE TABLE entries (
-    id character varying NOT NULL,
-    title character varying NOT NULL,
-    updated timestamp with time zone DEFAULT now() NOT NULL,
-    author character varying,
-    content integer,
-    link integer,
-    category character varying,
-    published timestamp with time zone DEFAULT now(),
-    rights text
-);
-
-
---
--- Name: TABLE entries; Type: COMMENT; Schema: atom; Owner: axelrod
---
-
-COMMENT ON TABLE entries IS 'Source currently not supported.';
+    slug VARCHAR(96) NOT NULL,
+    published_date DATE,
+    published_time TIME(0) WITH TIME ZONE,
+    author VARCHAR(32),
+    category VARCHAR(32),
+    rights TEXT,
+    CONSTRAINT entries_pkey PRIMARY KEY (slug, published_date),
+    CONSTRAINT entries_valid_slug CHECK (slug ~ E'^[-\\_a-zA-Z0-9]{1,96}$'),
+    CONSTRAINT entries_author_fkey
+        FOREIGN KEY (author) REFERENCES people (name)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT,
+    CONSTRAINT entries_category_fkey
+        FOREIGN KEY (category) REFERENCES categories (term)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT
+);
+
+COMMENT ON TABLE entries IS
+'This table contains the information that should be the same for all
+revisions of a post.  As only the most current revision should be public, this
+table ensures that the author, categories and rights stay consistent across
+edits.';
+
+
+--
+-- The entry_revisions relation
+--
+
+CREATE TABLE entry_revisions (
+    slug VARCHAR(96) NOT NULL,
+    published_date DATE,
+    published_time TIME(0) WITH TIME ZONE,
+    updated TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
+    title VARCHAR(96) NOT NULL,
+    content INTEGER,
+    summary INTEGER,
+    CONSTRAINT entry_revisions_pkey PRIMARY KEY (slug, published_date, updated),
+    CONSTRAINT entry_revisions_entries_fkey
+        FOREIGN KEY (slug, published_date)
+        REFERENCES entries (slug, published_date)
+        ON UPDATE CASCADE
+        ON DELETE CASCADE,
+    CONSTRAINT entry_revisions_content_fkey
+        FOREIGN KEY (content) REFERENCES content (id)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT,
+    CONSTRAINT entry_revisions_summary_fkey
+        FOREIGN KEY (summary) REFERENCES content (id)
+        ON UPDATE CASCADE
+        ON DELETE RESTRICT
+);
+
+COMMENT ON TABLE entry_revisions IS
+'This table has information that is able to be unique to a particular edit.  The
+slug and published relate to the ''entries'' table.  The title can change in a
+particular revision, as can the references to the content and summary.';
 
 
 --



--
-- Name: entries_pkey; Type: CONSTRAINT; Schema: atom; Owner: axelrod; Tablespace: 
--

--ALTER TABLE ONLY entries
--    ADD CONSTRAINT entries_pkey PRIMARY KEY (id);


--
-- Name: feeds_pkey; Type: CONSTRAINT; Schema: atom; Owner: axelrod; Tablespace: 
--




--
-- Name: feeds_link_fkey; Type: FK CONSTRAINT; Schema: atom; Owner: axelrod
--

