[Systers-dev] Database Schema for Message Storing

[Robin Jeffries]

This is great (and very quick of you).  A few comments.  I am not a database
expert, so someone with real database knowledge should chime in.

1.  I think you should not have messageid and message_id for two different
things.  I can imagine a number of hard to track down bugs that this is
likely to lead to.  message_id is the one that we use already and is mailman
specific, so it probably needs to stay as is.  Not sure what to call the
other, maybe email_message_id?

2.  It's my belief/superstition that it's better to have separate tables for
different actions (if nothing else, it leads to less confusion when there
are multiple accesses of the database).  So I would make this a new table,
with all these fields.  (Now would be a good time for the database expert to
speak up)

3.  A few more fields from the message I would consider.  You may not need
them now, but I can imagine a future where you need them
   - a field for the sender email, as the sender could have used one of her
alternative addresses, (and for a reason -- e.g., for anonymity).  In fact,
I'm a little worried about a leak here.  If someone posts under a pseudonym,
there shouldn't be a way for a regular user to find out the real name/email
behind the pseudonym (an admin should be able to, but should have to work at
it), and having the sender field yoked to the users mailman record makes it
easy for someone to decide to expose that.
   - a field for the cc field (for both to and cc, keep in mind that it can
have several entries, and those entries consist of a name and an email
address, the name part being optional.  You might think about how to
structure them
   - a field for ALL the message headers (including the ones you have called
out into separate fields).  Could be useful for many things -- sending mail
to different mail clients in different formats, figuring out which messages
went through which intermediate points, searching for meta strings that you
aren't sure which field they are in -- I really don't know, but I wouldn't
want to lose that information

What time is the date in?  UTC time?  How will you display it for a user?
 Especially if it is recent, they will want to see it relative to their
timezone.  Can you do that?

Robin

On Thu, Jul 22, 2010 at 5:19 PM, Yian Shang <yian.shang at gmail.com> wrote:

> I've written a bit about the database schema for message storing here:
> http://systers.org/systers-dev/doku.php/database-schema
>
> Since the current db already had a few fields for message identification, I
> added a few more ones that I thought would be useful for search/UI. I've
> talked to Priya about the fields and I think I integrated the ones that she
> was using with Whoosh. Please let me know if more fields/other
> modifications
> are needed.
>
> --
> Yian || http://movicont.nfshost.com/
>
>
> To unsubscribe from this conversation, send email to <
> systers-dev+database3+unsubscribe at systers.org<systers-dev%2Bdatabase3%2Bunsubscribe at systers.org>>
> or visit <
> http://systers.org/mailman/options/systers-dev?override=147&preference=0>
> To contribute to this conversation, use your mailer's reply-all or
> reply-group command or send your message to
> systers-dev+database3 at systers.org <systers-dev%2Bdatabase3 at systers.org>
> To start a new conversation, send email to <systers-dev+new at systers.org<systers-dev%2Bnew at systers.org>
> >
> To unsubscribe entirely from systers-dev, send email to <
> systers-dev-request at systers.org> with subject unsubscribe.
>