Page 2 of 2

Posted: Tue Aug 16, 2005 12:51 pm
by nielsene
I don't think the single table versus multiple tables is a scaleability issue. With proper views/stored procedures or even just a single point access from PHP you can completely hide the multi-table layout, plus each table is more aligned with its use (no generic parameter coluumns and said columns can refer to appropriate other tables where needed.)

I think if I were forced to work under the common versions of MySQL, I'd might agree with you, though, so your advice might be right here. (I typically only use PostGreSQL so I'm more familiar with its capabilities)

Posted: Tue Aug 16, 2005 1:05 pm
by evilmonkey
nielsene wrote:Are all messages point-to-point or do you allow broadcast? Ie, i the "To" line always one or more usernames, or is there an option to mail to all people of a given category, etc?
Messages are point-to-point, but the TO: line may have more than one name in there. These are comma separated. The administrators may message all the users, but the individual users will not be given broadcast rights in the interest of hightened security and control of spam.

Posted: Tue Aug 16, 2005 1:16 pm
by feyd
true, it can be masked effectively under an object, and I have no issue with it. It should be another avenue to research, depending on the end complexity and stuff.

Another thought along the multiple table route (which I have no argument against really) is you can make a logical combination system with those as well without too much trouble. So both are quite valid.

Posted: Tue Aug 16, 2005 1:26 pm
by nielsene
OK, here's what I'm starting to think is "good":

Because of the antipated size of the site, you want to avoid either of the following two types of checks:

1. Get all legal people to mail to, test if inside that -- likely very bad
2. Get all illegal people, test if not in, likely very bad as well

I think something like: (assuming no category-whitelist, only user-whitelist, if any)
1. Convert the list of "to"s to a comma seperated list of userids AND a normal array of userids
2. Handle Explicit whitelists:

Code: Select all

SELECT userid FROM user_whitelist WHERE userid IN ($toList);";
Mail the PM to any userid returned and remove from the array, regenerating the comma list
3.Handle easy blacklist:

Code: Select all

SELECT userid FROM user_blacklist WHERE userid IN ($toList);
Remove any returned ids from the array and regenerate
4. Now we deal with the filtering blacklists having eliminated all of the "easy" cases:

Code: Select all

SELECT userid FROM ..... WHERE userid IN ($toList);";
Now the middle "..." is still a hard part, but it depends greatly on the exact design details, HOWEVER, maintaining the list of remaining "tos" to deal with and always using in in the queries will help to greatly constrain any queries and keeps them efficient, as well as getting rid of any looping over the toList thereby avoiding query multiplication.

Posted: Tue Aug 16, 2005 9:09 pm
by evilmonkey
Alright, thank you very much Eric and feyd. I will get back to this over the next coupld of days (writing a few easier parts first), and if I have any questions, I will post back here. Overall, Eric, I think I will impliment a system similar to the one you suggested in your last post.

Thanks for the help gentelmen. :)

Posted: Tue Aug 16, 2005 9:50 pm
by nielsene
No problem.

When you get back to the difficult part, if you need more help, I think it would be helpful if you could show a mock-up of the form you'ld like people to use to select what categories they want to block. Seeing how the choices will be presented will help me better understand how to advise you on the database structure and queries.

Good luck!