[Wplug-web] db schema

[Robert Dale]

On 25 Mar 2001 markd at badgertronics.com wrote:

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

On behalf of evand, jo2y, and myself, danke :)

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

Agreed.  Need to figure out where.

> > 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.

Right!

> 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.

We discussed this and all nodded that there would be only one 'minutes'
per event, however that now seems very limiting.  Good idea!

> >  user_id int4 references users(user_id)
> 
> what is the user associated with an event?  Organizer?  Speaker?
> Room unlocker?

The "user" of the event is the coordinator for that event.
The "user" of a location is the room's "owner".  It could be the
same as the coordinator, or someone very different.

> > 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.

Not really.  And going with your minutes modelling, we can have a user
without any preferences.  But we can't have preferences without a user.

> > 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.

Our idea here is this would be a list of peoples' names.  The field
should have been marked text.  We figured it would be too tedious
to select existing members in one place then enter non-members in some
other list.

I'll make these changes and add some additional rationale.

-- 
Robert Dale

wplug member since 1998