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?
Theory: Storing Private Messages w/minimal load
Moderator: General Moderators
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
- 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
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)
Re: Theory: Storing Private Messages w/minimal load
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.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?
- 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
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.Are there any other ways to quickly find where the data is located while creating as minimal amount of load/queries as possible?
Re: Theory: Storing Private Messages w/minimal load
I agree with Bill H completely.
- 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
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! 