[Wplug-web] db schema 0.02

[Robert Dale]

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

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


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

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


--- locations for events
--- since locations can be reused, it's nice to have a moniker for selection
--- moniker example: CMU NSH Rm.XXX
--- name exmaple: CMU Neil Simon Hall Room XXX
--- location can be physical, on irc, or at a url (ie. radio broadcast)
--- the location user is the location's real owner - could be the same
---   as the event user.

create table locations (
  location_id serial primary key,
  moniker varchar(16) unique not null,
  name varchar(64) not null,
  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 not null,
--  map, -- need more details
  user_id int4 references users(user_id),
);


--- minutes taken at events
---   attendees is a list of peoples' names

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


--- users table
--- a member is someone with a password
--- we use this table for storing presenter contact info
--- we would like to be able to determine if the user is a member
---   or not by checking if the password is null

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


--- 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
--- this is used by the self-serve web form - an admin manually entering user
---   info should not go in here

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


--- presentations at events
---   while presenters don't have to be members, we are reusing the
---   user's table to store their info.
--- presentations are not reused even if they are repeated
---   (removed the presentation:event map)

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


--- website session data

create table sessions (
  session_id serial primary key,
  session_hash varchar(64) unique not null,
  ip_addr varchar(128) not null,
  expirey timestamp not null,
  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 default now(),
  expirey timestamp,
  topic varchar(64) not null,
  body text not null,
  user_id int4 references users(user_id),
  event_type_id int4 references event_types(event_type_id)
);


--- capabilities
---  "use", "create", "modify", "delete"

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


--- capability groups
--- "news admin", "events admin", "minutes admin", "superuser"

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


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),
  user_id int4 references users(user_id)
);


-- preference name (ie. "favorite dist")

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


-- 
Robert Dale

wplug member since 1998