[Wplug-web] db schema

[markd at badgertronics.com]

Oooh, data models. I like data models.  And this is a good one.


Having 'not null' constraints (where appropriate) would be good.  Put
some of the burden for data integrity on the database.


> create table events (
>   event_type_id int4 references event_types(event_type_id),
>   location_id int4 references locations(location_id),

>   minutes_id int4 references minutes(minutes_id),

I'd recommend inverting the minutes - e.g. removing minutes_id from
events, and making the minutes table reference the event.  You generally
want the columns of a table to directly reflect what the object is
you're modeling.  So, an event cannot exist without a type, and an
event cannot exist without a location, but an event can exist without
minutes.

Also, we may want to have multiple "minutes" per event.  Say the ones
DT writes, and then the ones evanD writes which take exception to DTs,
etc.  Minutes could also be expanded in concept to 'user reports', say
from the people personing the table at a computer show.

>  user_id int4 references users(user_id)

what is the user associated with an event?  Organizer?  Speaker?
Room unlocker?



> create table users (
>  preference_id int4 references preferences(preference_id)

Can preferences be shared amongst users?  If not, I'd remove this
and add a 'user_id' column to preferences.


> 
> create table minutes (
>  attendees int4 references users(user_id)

with this, you'd need a row in minutes for each user attending.
Also, what about attendees that aren't users?
Since it's many-to-one, a user:event map for attendees may be more
useful.

Gotta run, I'll pick at his some more later on.
++md