Matthew T. O'Connor wrote:
On Wed, 2003-07-02 at 11:44, Jesse Norell wrote:
With upcoming schema changes (cached headers, etc.), it'd
probably be worth stepping back and looking at the best way to
handle message storage, and see what improvements can be made.
Even something as simple as a messageblk flag as to whether the
block contains headers or the body could fix the above issue.
While on the subject of storing messages, I don't know that
filesystem files are suited to dbmail as it complicates database
replication, which is certainly a desired feature.
Where is the schmea changes are being discussed? I haven't seen any
mention of it anywhere, I would like to be part of the discussion.
A quick design: perhaps something like the following.
messages: One row per message similar to the current messages table
except without all the flags.
message_flags: Keeps all the flags (seen, answered deleted).
id int4 primary key
flag_id int2 (references a list of possible flags in another table)
flag_value int2 (value of flag).
the advantage to this type of setup is that you use zero space for flags
that are equal to the default value. Also, when a flag changes it only
updates a very small row, now a larger row that has several flags. Also
it allows the definition of additional flags without changing the
database schemea.
This also reduces the number of indexes.
message_headers: similar to the current message_blks table except it
only keeps the portion of the message that contains headers. This fixed
the problem mentioned of a large set of headers and also easily flags
the message block that contains all the header information
Should this not be part of the messages table ?
message_body: contains the remainder of the message, could be broken up
like the current message_blks table or not, I would prefer not, but I
don't know if mysql is up to it.
Thoughts?
The message contents and headers should be stored in a binary type
rather than a text type so encoding does not become an issue.
