--
-- A database of "events" to which a community can contribute.
--
-- There are three basic entities: events, agents, and blurbs.
--
--  > An event is a unique point of data with a complex structure.
--  > An agent is essentially a user who can log into the application.
--  > A blurb is a text comment which can be attached to an event.
--  > Blurbs know which agent added them.  Blurbs can be edited later.
--

CREATE TABLE agents
    (  agentid SERIAL UNIQUE,
       agent TEXT NOT NULL,
       biography TEXT,
       PRIMARY KEY agentid
       ) ;

CREATE TABLE blurbs
    (  blurbid SERIAL UNIQUE,
       blurb TEXT NOT NULL,
       agentid INTEGER NOT NULL REFERENCES agents (agentid),
       PRIMARY KEY blurbid
       ) ;

CREATE TABLE events
    (  eventid SERIAL UNIQUE,
       event TEXT NOT NULL,
       -- other fields of interest
       PRIMARY KEY eventid
       ) ;

CREATE TABLE event-blurbs
    (  eventid INTEGER NOT NULL REFERENCES events (eventid),
       blurbid INTEGER NOT NULL REFERENCES blurbs (blurbid)
       ) ;