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?
TIA,
Daren Cotter
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
