Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General Discussion » View Thread |
Messages 1 to 10 of 20 total |
How to keep tabs on who has seen what? |
Wed, Jul 4 2007 4:00 AM | Permanent 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 AM | Permanent 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 AM | Permanent 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 AM | Permanent 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 AM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 <<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 PM | Permanent 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 PM | Permanent 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 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Thursday, March 28, 2024 at 06:05 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |