Theory: Storing Private Messages w/minimal load

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Theory: Storing Private Messages w/minimal load

Post by JAB Creations »

I'm currently working on a private message feature and I'm trying to think of the best way in which private messages are stored. I would presume in their own table. Maybe have id_from and id_to columns? I'm not sure how else I could approach the structure however. If you list your private messages then in example you have have a message on rows 1,3, 6, and 22. I'm interested in how larger sites deal with sifting through tons and tons of data. Perhaps in example the row numbers are imploded to the user's account row? Then instead of searching through a very large database it simply knows to pick the first set of ten rows based on the first ten numbers exploded from the private message column per user. This is just now formulating in my mind...

So let's say you have messages in the private_message table on rows 1,3,6,22 (that being the imploded string containing the row id's from which the site chooses the messages). Is this a good setup for sites that may eventually scale to a very large size? Are there any other ways to quickly find where the data is located while creating as minimal amount of load/queries as possible?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Theory: Storing Private Messages w/minimal load

Post by Christopher »

It is probably best to simply save the User ID with the message. Then "SELECT messagetext FROM message WHERE userid=$userid". Trying to do tricks like saving ID like you are suggesting will cause more troubles that good. If you need to reinvent the wheel -- go for it. But I would suggest the simplest solution first.
(#10850)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Theory: Storing Private Messages w/minimal load

Post by califdon »

JAB Creations wrote: ... row numbers are imploded to the user's account row? Then instead of searching through a very large database it simply knows to pick the first set of ten rows based on the first ten numbers exploded from the private message column per user. This is just now formulating in my mind...

So let's say you have messages in the private_message table on rows 1,3,6,22 (that being the imploded string containing the row id's from which the site chooses the messages). Is this a good setup for sites that may eventually scale to a very large size? Are there any other ways to quickly find where the data is located while creating as minimal amount of load/queries as possible?
Definitely avoid storing multiple values in a column. That violates First Normal Form of database normalization. Values must always be "atomic", that is, a column can represent only ONE value, not a series of values. I know it's tempting to want to do something like you described, but it will come back to bite you in the you-know-where. A denormalized table makes it impossible for SQL to do what it's designed to do.
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: Theory: Storing Private Messages w/minimal load

Post by Bill H »

Are there any other ways to quickly find where the data is located while creating as minimal amount of load/queries as possible?
I believe you are overthinking the problem. Databases exist specifically for the purpose of making queries on them. The more discrete the manner is of storing the data, the more complex the queries need to be, yes, but that's a good thing, not a bad thing. It helps the database be more selective in finding data for you and, since it's designed for that purpose, it does that very happily.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Theory: Storing Private Messages w/minimal load

Post by califdon »

I agree with Bill H completely.
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Theory: Storing Private Messages w/minimal load

Post by JAB Creations »

Thanks guys, I have moved to this thread of which someone has already helped me resolve things at that end. I'm having a lot of fun with this stuff considering I'm using what I've already learned along with all the progress I'm making...so long as I can concentrate that is! :D
Post Reply