[Wplug-web] db schema

[Robert Dale]

--- stores events.  any sort of group activity is an event

create table events (
  event_id serial primary key,
  name varchar(32),
  topic varchar(64),
  date timestamp default now(),
  event_type_id int4 references event_types(event_type_id),
  location_id int4 references locations(location_id),
  minutes_id int4 references minutes(minutes_id),
  user_id int4 references users(user_id)
);

--- event types are like "GUM", "Installfest", "Other"

create table event_types (
  event_type_id serial primary key,
  event_type_name varchar(32)
);

--- locations for events
--- location can be physical, on irc, or at a url (ie. radio broadcast)

create table locations (
  location_id serial primary key,
  moniker varchar(16) unique,
  address varchar(64),
  city varchar(24),
  state varchar(12),
  zip_code varchar(10),
  country varchar(12),
  irc_channel varchar(16),
  url varchar(80),
  phone varchar(16),
  directions text,
--  map, -- need more details
  user_id int4 references users(user_id),
);

--- minutes taken at events

create table minutes (
  minutes_id serial primary key,
  author int4 references users(user_id),
  subject varchar(64),
  body text,
  attendees int4 references users(user_id)
);

create table users (
  user_id serial primary key,
  date_created timestamp default now(),
  first_name varchar(16),
  last_name varchar(24),
  email varchar(64) unique,
  password varchar(8),
  url varchar(80),
  primary_phone varchar(16),
  secondary_phone varchar(16),
  city varchar(24),
  state varchar(12)
  preference_id int4 references preferences(preference_id)
);


--- confirm table is a temporary store while we verify the email address
--- when we get back a matching hash, we create the account
--- these fields are therefor require info

create table confirm (
  confirm_id serial primary key,
  hash varchar(64) unique,
  expire_date timestamp,
  first_name varchar(16),
  last_name varchar(24),
  email varchar(64) unique,
  password varchar(8),
  url varchar(80),
  city varchar(24),
  state varchar(12)
);

--- presentations at events

create table presentations (
  presentation_id serial primary key,
  topic varchar(64),
  presentation_url varchar(128),
  user_id int4 references users(user_id)
);

--- there can be many presentations per event

create table event_presentations_map (
  presentation_id int4 references presentations(presentation_id),
  event_id int4 references events(event_id)
);

--- website session data

create table sessions (
  session_id serial primary key,
  session_hash varchar(64) unique,
  ip_addr varchar(128),
  expirey timestamp,
  user_id int4 references users(user_id)
);

--- news can be generated from an event or stand by itself
--- news types are event types since news describes some sort of an event

create table news (
  news_id serial primary key,
  date timestamp,
  expirey timestamp,
  topic varchar(64),
  body text,
  user_id int4 references users(user_id),
  event_type_id int4 references event_types(event_type_id)
);

--- capabilities

create table capabilities (
  capability_id serial primary key,
  name varchar(16)
);

create table groups (
  group_id serial primary key,
  name varchar(16)
);

create table group_capability_map (
  group_id int4 references groups(group_id),
  capability_id int4 references capabilities(capability_id)
);

--- user preferences

create table preferences (
  preference_id serial primary key,
  preference_name_id int4 references preference_names(preference_name_id),
  preference_data varchar(24)
);

create table preference_names (
  preference_name_id serial primary key,
  preference_name varchar(24)
);

-- 
Robert Dale

wplug member since 1998