Hello Jeff,
From your database design it looks like that you change the
mailbox_idnr record in the messages table when you move mail from one
mailbox folder to another. I hope this is possible at least by the IMAP
protocoll?
The problem as I see it is the relational database structure itself. You
have this pointing relation MESSAGEBLK->MESSAGE->MAILBOX->USERS, quite
the opposite to when you are programming linked trees on a computer.
I beleive you could achevie this by another database layer, one for each
user and messageblk. Let's divide the messageblk into two parts
msgblkptr (MessageBlockPointer) and msgblkdata (MessageBlockData).
The pointer is unique for every user and points to the Data.
So if you send 1000 people a message you will get 1000 pointers, but
just one datablock.
When you delete a pointer, you check if there are any more pointers to
the block, if the ointercount is zero, you also delete the data block.
What is wrong with my reasoning?
Except for that this will be difficult to merge into the installed base.
What will the performance hit be?
Is it worth the effort of changing?
Please explain the problem of crosslinking.
Is this one of exchanges weak points?
/Magnus
Jeff Brenton wrote:
Hello Magnus,
MS> Is there one new MESSAGEBLK created in the database for each user for
MS> these messages, or is the MESSAGEBLK shared between the users.
Yes, there is a complete message (1 entry into message and 2 or more
into messageblk tables) for each recipient. Send a message to 1,000
people on the system, and 1,000 copies of it exist.
The only system that does NOT do that, to my knowledge, is Exchange
Server; it maintains a separate table internally to keep track of
which users have seen/deleted the message, and doesn't actually delete
it until ALL have seen it.
While this is more space efficient, it is also a nightmare if certain
users decide to move the message to one of their mailbox folders
(maintained on the server, rather than on their local machine),
because the copy makes a new link... message bodies can be cross
linked endlessly, making problems if you ever need to restore the
database.
At least some backup procedures "fix" this by saving individual
"snapshots" of the tables, so you end up with a backup containing a
complete copy of these group messages for each user who hasn't purged
it from their mailbox, and the restore ends up having multiple copies
anyway...
