Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 20 total
Thread How to keep tabs on who has seen what?
Wed, Jul 4 2007 4:00 AMPermanent Link

Let's say I have a database with records that a number of readers must
read. Readers can be of any number, and added at any time. Each one may
read the records any in any order. There will be thousands of records in
the main table. What is the best way in space and efficiency to keep a
record of which records that each reader has read? SQL or direct is quite
acceptable.

I have an idea, but it isn't fully formed, and I'd like suggestions
untainted by my thought.

/Matthew Jones/
Wed, Jul 4 2007 6:35 AMPermanent Link

"Harry de Boer"
Matthew,

I guess a table with
id_reader PK
id_message PK

When a reader reads the message a record can be added. Also eay to query.
The two fields are the primary key (to be sure that only one pair of
reader-message will exists. If you want to hold the number of times a
message is read too then simply add an integer field 'read' with an defalut
value of 1 and increase that value of this on every read.

Regards, Harry



"Matthew Jones" <matthew@matthewdelme-jones.delme.com> schreef in bericht
news:memo.20070704085703.4572G@nothanks.nothanks.co.uk...
> Let's say I have a database with records that a number of readers must
> read. Readers can be of any number, and added at any time. Each one may
> read the records any in any order. There will be thousands of records in
> the main table. What is the best way in space and efficiency to keep a
> record of which records that each reader has read? SQL or direct is quite
> acceptable.
>
> I have an idea, but it isn't fully formed, and I'd like suggestions
> untainted by my thought.
>
> /Matthew Jones/

Wed, Jul 4 2007 6:39 AMPermanent Link

Chris Erdal
matthew@matthewdelme-jones.delme.com (Matthew Jones) wrote in
news:memo.20070704085703.4572G@nothanks.nothanks.co.uk:

> Let's say I have a database with records that a number of readers must
> read. Readers can be of any number, and added at any time. Each one
> may read the records any in any order. There will be thousands of
> records in the main table. What is the best way in space and
> efficiency to keep a record of which records that each reader has
> read? SQL or direct is quite acceptable.

Matthew,

Firstly, what do you want to know:

1/ Who has/hasn't read this record?
or
2/ which records has/hasn't this reader read?

Secondly, I'd be tempted to use a separate table with one row for each
reader or record, and an open bitmap of autoinc ID field numbers from the
other table, using hyperstring functions.

i.e. key field = the records table ID with a bitmap of reader IDs that
have read it in there, or vice-versa.

If almost everyone reads almost every record in the end, you could
perhaps try a cross-tab table where you insert a row for every reader
with each new record number, and a row for every (non-archived) record
with each new reader. You then remove each row when the reader opens the
record. That way the table never gets too huge, but the inserts may slow
things down a bit. Perhaps a separate thread could do that part...

--
Chris
(XP-Pro + Delphi 7 Architect + DBISAM 4.25 build 4 + EDB 1.04 build 3)

Wed, Jul 4 2007 7:35 AMPermanent Link

I hadn't actually thought of having a single table for all readers - a
nice point I should have thought of. There is no need to know how often it
is read.

/Matthew Jones/
Wed, Jul 4 2007 7:35 AMPermanent Link

> 2/ which records has/hasn't this reader read?

This. It is basically for a shared email system, where I'd want to know
who has read a message, or rather, who needs to see the message still.

A bitmap was what I had thought of. Well, sort of. In a comms application
I once designed a "rolling window" for the packets as it was a multi-
channel system. Essentially (in terms of my current problem) it knew the
lowest record that had not been read, and then used a 64-bit bitmap to
represent others. When the lowest one is then read, the bitmap is shuffled
down. In the comms case, 64 records "window" was enough, but wouldn't work
for the thousands of records. Thus I thought it worth asking.

It strikes me that having all the unread flags in one table with the
reader ID (thanks Harry!) means that a reader can also mark a message as
unread for others if appropriate. This is email, so something not relevant
to all could be automatically skipped and save time (as opposed to
deleted). Hmm.

I wonder actually if a simple bitmap is actually a better idea. 200,000
records would take 25,000 bytes. If I used code to manage that, and
truncate previous items like the comms window, then it would work okay.

One aim here is to be resilient against crash - I don't want the unread
markers to be lost if the software crashes. Hmm, how about I do it the
other way round? That's more sensible surely? All messages are read by
default. Thus the table for a reader starts empty. Then, as they read the
records, the "first unread" value is set to the next message. If they then
read out of order, I remember that record number. And then when they read
past that by catching up, the individual record items are removed. The
read fields could be done by bitmap again, or record.

I think inverting the problem makes it more manageable. I'll think
further, and welcome more comments.

/Matthew Jones/
Wed, Jul 4 2007 8:25 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Mathew

This is something I'm interested in as well since I redeveloping an app which has email and I want to allow it to do the standard thing of not read = bold.
My initial thoughts are along the lines of have an extra blob field and store user ID's  in when read.

I'm not going to be producing reports of who has/hasn't read an email simply reflect that someone has read it.

Its an approach  use a fair bit because it means that delete the record and its all gone, no need to delete stuff from other tables. Its also fairly compact
(generally only one person will read the email) and its an easy test eg Pos(IDstring,tableHasBeedReadAsString)=0

Make sure you let us know which solution you eventually pick so I can copy it if its better than my approach.

Roy
Wed, Jul 4 2007 9:05 AMPermanent Link

Tell me more about your email app! At the moment mine is just speculative
due to not being able to find anything that fits our requirements. It is a
matter of working out how I'd do it, and then fitting stuff in in the
background. I'm always on the lookout for shortcuts.

If you just need a marker for everyone, then that can be in the main mail
record. Are you storing the email in the database too? I'm pondering doing
what an email package we use now does and it just keeps all the text in a
separate file of plain text with markers for separation. That way the
bulky text is stored most efficiently. The database part just has an
offset/length into this file.

Hmm, having re-read your idea, I think you are saying that you would have
the memo per message, and a read then causes you to add the user ID to the
string. That would work well, except for when you add a new reader, or
want to do a "mark all read". That would cause you to have to traverse all
emails. But on the other hand, if you generally show only those not read
by anyone, then it wouldn't matter.

One of the issues I'm looking to resolve is that we want a multi-reader
email system, combined with the benefits of a "help-desk" system so we can
ensure prompt responses. Your idea may work well for that.

/Matthew Jones/
Wed, Jul 4 2007 10:51 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate


<<Tell me more about your email app! >>

You can have a look at an older version of the finished single user article

http://www.nlh.co.uk/freebies.html

<<If you just need a marker for everyone, then that can be in the main mail
record. Are you storing the email in the database too? >>

Yes and with full text indexing!

<<I'm pondering doing
what an email package we use now does and it just keeps all the text in a
separate file of plain text with markers for separation. That way the
bulky text is stored most efficiently. The database part just has an
offset/length into this file.>>

That's sort of like Outlook Express - separate files for each mailbox - if anyone grows to much it corrupts and its a PITA for searching

<<Hmm, having re-read your idea, I think you are saying that you would have
the memo per message, and a read then causes you to add the user ID to the
string.>>

Exactly

<<That would work well, except for when you add a new reader>>

You do absolutely nothing in that circumstance since the new reader HASN'T read the email (I guarantee it Smiley

<<want to do a "mark all read". That would cause you to have to traverse all
emails. But on the other hand, if you generally show only those not read
by anyone, then it wouldn't matter.>>

Surely you will split the emails into mailboxes, I'm thinking of applying a fulltext index to the field so you never "mark all read" you "mark all read in this
mailbox that haven't been read by this reader" probably never more than a handfull, but even if a couple of hundred it won't take to long.

<<One of the issues I'm looking to resolve is that we want a multi-reader
email system, combined with the benefits of a "help-desk" system so we can
ensure prompt responses. Your idea may work well for that.>>

Benefits - help desk - oxymoron

Roy

ps if you want to call and talk about it the phone number is on the web site
Wed, Jul 4 2007 12:02 PMPermanent Link

> Surely you will split the emails into mailboxes

That's the big issue for us. While we have folders, we all share a common
email box. All email is read using Virtual Access
(http://www.virtual-access.org/) which allows us all to see conversations
going on. This is important as some people are part time, or may be away
for a few days. We don't want the email sent to one person that says they
need an answer by noon to be not responded too. By all sharing the same
database, we don't miss this.

However, there are problems with VA that we'd like to resolve, and I had a
good look at http://www.cerberusweb.com/ which does the help desk system
well. I'm pondering a mix between the two, so that some people can respond
to open queries, and others can view the history. Of course it will take a
lot of time, but I want to study the feasibility of it for now.

I'll have a play with TMaN, and see what I can learn from it. Thanks. No
need to speak at the moment - it would stop me thinking 8-).

/Matthew Jones/
Wed, Jul 4 2007 12:38 PMPermanent Link

> I'll have a play with TMaN, and see what I can learn from it.

It is most interesting. There is a mention of "if you don't like it,
change the source". Is that option available? There are a few things I'd
want to sort - reading some of our email got me an error message, and I
can't see how to move suspicious email to the inbox. And it started
downloading all the images for spam emails, but once I found the option to
turn that off, it didn't download when specifically asked. Also, one email
with multi-part display only showed the footer and not the main body. But
it would be good to have a play with it further. I have no budget for it
of course. 8-( I'd like to know where you are going with it too - for a
pet project it has obviously developed a long way.

/Matthew Jones/
Page 1 of 2Next Page »
Jump to Page:  1 2
Image