RE: normalization question - Rene Churchill - com.mysql.lists.mysql

4 messages in com.mysql.lists.mysqlRE: normalization question

From	Sent On	Attachments
Daren Cotter	18 Aug 2001 15:03
Rene Churchill	19 Aug 2001 07:33
Daren Cotter	19 Aug 2001 15:54
Rene Churchill	20 Aug 2001 05:43

Subject:	RE: normalization question
From:	Rene Churchill (re...@vtwebwizard.com)
Date:	08/20/2001 05:43:27 AM
List:	com.mysql.lists.mysql

You'll need a left join to do that query. Something like:

select member.id, member.email, count(xref.email_id) as not_read from member left join xref on member.id = xref.member_id where not_read == 0 and xref.email_id = 283 group by member.id;

where xref is the email/member cross reference table and 283 is the particular email you're running the query about.

Rene

At 03:54 PM 8/19/01, Daren Cotter wrote:

The data wouldn't need to be stored for any longer than two to three months, so that shouldn't be a problem...what about my query to get all members that have not read the mailing, is that possible (assuming I don't use the reverted logic you were talking about).

-----Original Message----- From: Rene Churchill [mailto:re...@vtwebwizard.com] Sent: Sunday, August 19, 2001 7:34 AM To: Daren Cotter Cc: mys...@lists.mysql.com Subject: Re: normalization question

Hi Daren,

How long do you need to store this information? The normalized table that you describe is simple and easy to index. Having several million rows in the table won't bother MySQL. So is it sufficient to keep the data around for a month and then prune it out of the database? Long term records could be dumped out into text logfiles for safe keeping.

Another possiblity is to invert your storage logic. By that, I mean insert a row in the cross-reference table for every user that has NOT read the email. Then when they read it, remove that row from the table. This will automatically prune down the table size as more and more of your readers check their email.

I would HIGHLY suggest using the DELAYED option on that size of an insert.

You may not want to go this logic inversion route because of the havoc it will play with other queries. For example, since any user that does NOT have a matching row in the cross-reference table is assumed to have read the message, any new user joining you will automatically be assumed to have read all messages to date.

Rene

Daren Cotter wrote:

I have a table which needs to store which emails each member has read and had their account credited for. Currently, I have a field "read_array longtext" in my main members table, and each time a member reads an email, it simply concats to the end of this array. For example, they read email 288, their read array is:

288

They read 298, the read_array is:

288||298

Using this method (which is far from normalization, I know), I have been able to accomplish the two major things I need to be able to accomplish:

1) Insure that no member can get credit for reading the same mailing twice (check their current read_array, using ereg()) 2) Run a query to see how many members have read each mailing (WHERE read_array LIKE '%mail_id%')

I know that both of these tasks would be easier if I created a new table, and stored the member_id, mail_id, and date, and the table would then be normalized...however, I send approximately one new mailing per day, and this would mean 100,000 new rows in this table every day. Can somone offer me "expert" advice as to which method is better? Using an array that will grow to unlimited size, or using a table that is going to grow much larger every day?

Also, as a deterrant to switching to the normalized method...I frequently run queries to do the following:

*) After I send a mailing, a week later, I send the mailing again to all members who did not confirm reading the email. My Query is like: select member_id, (more) FROM members WHERE read_array not like '%mail_id%'

Is there any possible way I could accompish this task in one query if I had this process normalized?

To request this thread, e-mail <mysq...@lists.mysql.com> To unsubscribe, e-mail <mysql-unsubscribe-mysql=inbo...@lists.mysql.com> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php