The "Freeola Customer Forum" forum, which includes Retro Game Reviews, has been archived and is now read-only. You cannot post here or create a new thread or review on this forum.
I'm about to start writing a forum in Perl CGI (may convert it to PHP when I get the chance of learning PHP), using a MySQL database, and I'd like some advice on how to set out the database. My question is: should I have a seperate table for each forum, or should I have all messages for all forums in one table, and have a column for 'Forum Name' and query out each forum as needed?
Cheers,
Rikki
Cheers,
Rikki
Not really much you can do to speed up searches on text. If you visit any highly used forum that has search facility, you'll find its quite slow. Stuff like VBulletin accepts that searching is a slow operation and throws up please wait page while it does it for instance.
You could create a word index. 2 tables, 1 contains the word, the other contains link between word and document. Thats 1 possible one way of doing it but its difficult to know at what point you'd really benefit from having such a facility. Initially at least you'd probably find its slower than the standard method of doing text searches on your messages. And would be even slower if you have your posts spread over tables.
Something to remember, theres no such thing as the perfect design. :)
You can only anticipate so much and you can spend forever designing and refining. I'm sure what you've got is good enough, if its not cross that bridge when you come to it. If it needs a rewrite so be it. :)
-G
You could create a word index. 2 tables, 1 contains the word, the other contains link between word and document. Thats 1 possible one way of doing it but its difficult to know at what point you'd really benefit from having such a facility. Initially at least you'd probably find its slower than the standard method of doing text searches on your messages. And would be even slower if you have your posts spread over tables.
Something to remember, theres no such thing as the perfect design. :)
You can only anticipate so much and you can spend forever designing and refining. I'm sure what you've got is good enough, if its not cross that bridge when you come to it. If it needs a rewrite so be it. :)
-G
Yeh, that design is quite similar to what I was planning. Also, I was planning to use ForumID instead of the Forum name, but I was in a rush when typing the message, so it was a temporary oversight ;)
Sorry for the confusion, the numbers I used were just to get the point accross about asking whether it's better to have one table for all messages, or to have 5 tables for each of the 5 forums, with roughly a fifth the number of messages in it. I have no idea what numbers I'll be looking at, so I'd like it to be as scalable as possible. The archiving idea is one solution.
You mention the slowness of using the LIKE command for searching. Is there any way around this (for allowing the user to search for text within all messages)? The only thing I could think of is to have some sort of intelligent keyword generator to process each message and extract keywords...
Cheers,
Rikki
Sorry for the confusion, the numbers I used were just to get the point accross about asking whether it's better to have one table for all messages, or to have 5 tables for each of the 5 forums, with roughly a fifth the number of messages in it. I have no idea what numbers I'll be looking at, so I'd like it to be as scalable as possible. The archiving idea is one solution.
You mention the slowness of using the LIKE command for searching. Is there any way around this (for allowing the user to search for text within all messages)? The only thing I could think of is to have some sort of intelligent keyword generator to process each message and extract keywords...
Cheers,
Rikki
If you searching for keywords within strings, you'll need to use the 'like' command. Any DBA will warn you about these as they are the slowest kind of search, even on a well designed database that is fully optimised.
Thinking aloud, how about a table design like this...
Table - ForumNames
Fields - ForumName, (other forum detail), ForumId
Index - ForumId
Table - Messages
Fields - Message, Date, (other message details), ForumId, MessageId
Index - MessageId, ForumId
Table - ArchivedMessages
Fields - Message, Date, (other message details), ForumId, MessageId
Index - MessageId, ForumId
Table - Users
Fields - UserName, (other profile bits), UserId
Index - MessageId, UserId
So when searching...
select fn.ForumName, u.UserName, m.Message, m.Date
from ForumNames fn, Messages m, Users u
where lower(m.Message) like '%mysql%'
and fn.ForumId = m.ForumId
and m.MessageId = u.MessageId
To be honest if you're talking about having only 1000 messages then the performance hit isn't going to be worth worrying about. But if you are worried, hows about archiving all messages that are over (say) a month. The user then has the choice to perform a quicker search on new records or a slower search over all messages.
Thinking aloud, how about a table design like this...
Table - ForumNames
Fields - ForumName, (other forum detail), ForumId
Index - ForumId
Table - Messages
Fields - Message, Date, (other message details), ForumId, MessageId
Index - MessageId, ForumId
Table - ArchivedMessages
Fields - Message, Date, (other message details), ForumId, MessageId
Index - MessageId, ForumId
Table - Users
Fields - UserName, (other profile bits), UserId
Index - MessageId, UserId
So when searching...
select fn.ForumName, u.UserName, m.Message, m.Date
from ForumNames fn, Messages m, Users u
where lower(m.Message) like '%mysql%'
and fn.ForumId = m.ForumId
and m.MessageId = u.MessageId
To be honest if you're talking about having only 1000 messages then the performance hit isn't going to be worth worrying about. But if you are worried, hows about archiving all messages that are over (say) a month. The user then has the choice to perform a quicker search on new records or a slower search over all messages.
Rikki wrote:
the forums...
>
> My problem with having it all in one table was possible searching
> problems, and the size of the table. Firstly, if I wanted to search
> for a string within a particular forum, I'd have to query to extract
> just that forum, as well as querying for the search string. Can I do:
> 'WHERE forum="Programming" AND msgtext="MySQL"'?
> Secondly, is the issue of size. Is it ok to have a table of, say 1000
> rows, or would it be better to have 5 tables of 200 rows, in terms of
> searching in, or just accessing *one* particular forum?
>
> Cheers,
> Rikki
The thing to remember is databases are designed to be incredibly fast at the type of things you're doing so I really wouldn't worry about optimizing things just yet. Its much more important to have a good database design, apart from anything else you'll find optimizing it much easier further down the line. The number of rows you're talking about at present are relatively small I probably wouldn't worry about optimizing (if necessary) until it ran into the tens of thousands if that.
Having said that text comparisons are one of the slowest things you'll do so you should avoid them where possible. For instance in your example
'WHERE forum="Programming" AND msgtext="MySQL"'.
That will work but you should avoid referencing your forums by their title/name, ideally you should have a forum table with an id field (preferrably indexed) and reference the forum by its id. Same is true for any table really, if possible, reference them by the indexed fields.
-G
the forums...
>
> My problem with having it all in one table was possible searching
> problems, and the size of the table. Firstly, if I wanted to search
> for a string within a particular forum, I'd have to query to extract
> just that forum, as well as querying for the search string. Can I do:
> 'WHERE forum="Programming" AND msgtext="MySQL"'?
> Secondly, is the issue of size. Is it ok to have a table of, say 1000
> rows, or would it be better to have 5 tables of 200 rows, in terms of
> searching in, or just accessing *one* particular forum?
>
> Cheers,
> Rikki
The thing to remember is databases are designed to be incredibly fast at the type of things you're doing so I really wouldn't worry about optimizing things just yet. Its much more important to have a good database design, apart from anything else you'll find optimizing it much easier further down the line. The number of rows you're talking about at present are relatively small I probably wouldn't worry about optimizing (if necessary) until it ran into the tens of thousands if that.
Having said that text comparisons are one of the slowest things you'll do so you should avoid them where possible. For instance in your example
'WHERE forum="Programming" AND msgtext="MySQL"'.
That will work but you should avoid referencing your forums by their title/name, ideally you should have a forum table with an id field (preferrably indexed) and reference the forum by its id. Same is true for any table really, if possible, reference them by the indexed fields.
-G
Garin wrote:
> rik wrote:
> I'm about to start writing a forum in Perl CGI (may convert it to
> PHP
> when I get the chance of learning PHP), using a MySQL database, and
> I'd like some advice on how to set out the database. My question is:
> should I have a seperate table for each forum, or should I have all
> messages for all forums in one table, and have a column for 'Forum
> Name' and query out each forum as needed?
>
> Cheers,
> Rikki
>
> Forums spread across multiple tables will make searching more
> difficult. If you want features like displaying the last 10 posts,
> more difficult when you have to use multiple queries and then compare
> the results.
> There aren't really any benefits to spreading the like data over
> tables like that.
>
> Easiest way to manage it is 1 table for messages, 1 table for forums.
> The forum table can contain the forum id, title etc. And in the
> messages table, just have a forum id field to use for selecting posts
> of a particular forum.
> You might consider a third table too to store topics, but that depends
> on the layout of your forum etc. and a bit beyond the scope of your
> question. :)
[Urgh, rewriting this as it couldn't find the server when I last tried to post it last time :(]
You're right about keeping it in one table to easily allow searching in all forums. One of the most annoying things I find is having to search each forum individually with the same search string, as the topic could be in any of the forums...
My problem with having it all in one table was possible searching problems, and the size of the table. Firstly, if I wanted to search for a string within a particular forum, I'd have to query to extract just that forum, as well as querying for the search string. Can I do: 'WHERE forum="Programming" AND msgtext="MySQL"'? Secondly, is the issue of size. Is it ok to have a table of, say 1000 rows, or would it be better to have 5 tables of 200 rows, in terms of searching in, or just accessing *one* particular forum?
Cheers,
Rikki
> rik wrote:
> I'm about to start writing a forum in Perl CGI (may convert it to
> PHP
> when I get the chance of learning PHP), using a MySQL database, and
> I'd like some advice on how to set out the database. My question is:
> should I have a seperate table for each forum, or should I have all
> messages for all forums in one table, and have a column for 'Forum
> Name' and query out each forum as needed?
>
> Cheers,
> Rikki
>
> Forums spread across multiple tables will make searching more
> difficult. If you want features like displaying the last 10 posts,
> more difficult when you have to use multiple queries and then compare
> the results.
> There aren't really any benefits to spreading the like data over
> tables like that.
>
> Easiest way to manage it is 1 table for messages, 1 table for forums.
> The forum table can contain the forum id, title etc. And in the
> messages table, just have a forum id field to use for selecting posts
> of a particular forum.
> You might consider a third table too to store topics, but that depends
> on the layout of your forum etc. and a bit beyond the scope of your
> question. :)
[Urgh, rewriting this as it couldn't find the server when I last tried to post it last time :(]
You're right about keeping it in one table to easily allow searching in all forums. One of the most annoying things I find is having to search each forum individually with the same search string, as the topic could be in any of the forums...
My problem with having it all in one table was possible searching problems, and the size of the table. Firstly, if I wanted to search for a string within a particular forum, I'd have to query to extract just that forum, as well as querying for the search string. Can I do: 'WHERE forum="Programming" AND msgtext="MySQL"'? Secondly, is the issue of size. Is it ok to have a table of, say 1000 rows, or would it be better to have 5 tables of 200 rows, in terms of searching in, or just accessing *one* particular forum?
Cheers,
Rikki
rik wrote:
> I'm about to start writing a forum in Perl CGI (may convert it to PHP
> when I get the chance of learning PHP), using a MySQL database, and
> I'd like some advice on how to set out the database. My question is:
> should I have a seperate table for each forum, or should I have all
> messages for all forums in one table, and have a column for 'Forum
> Name' and query out each forum as needed?
>
> Cheers,
> Rikki
Forums spread across multiple tables will make searching more difficult. If you want features like displaying the last 10 posts, more difficult when you have to use multiple queries and then compare the results.
There aren't really any benefits to spreading the like data over tables like that.
Easiest way to manage it is 1 table for messages, 1 table for forums. The forum table can contain the forum id, title etc. And in the messages table, just have a forum id field to use for selecting posts of a particular forum.
You might consider a third table too to store topics, but that depends on the layout of your forum etc. and a bit beyond the scope of your question. :)
-G
> I'm about to start writing a forum in Perl CGI (may convert it to PHP
> when I get the chance of learning PHP), using a MySQL database, and
> I'd like some advice on how to set out the database. My question is:
> should I have a seperate table for each forum, or should I have all
> messages for all forums in one table, and have a column for 'Forum
> Name' and query out each forum as needed?
>
> Cheers,
> Rikki
Forums spread across multiple tables will make searching more difficult. If you want features like displaying the last 10 posts, more difficult when you have to use multiple queries and then compare the results.
There aren't really any benefits to spreading the like data over tables like that.
Easiest way to manage it is 1 table for messages, 1 table for forums. The forum table can contain the forum id, title etc. And in the messages table, just have a forum id field to use for selecting posts of a particular forum.
You might consider a third table too to store topics, but that depends on the layout of your forum etc. and a bit beyond the scope of your question. :)
-G
I'm about to start writing a forum in Perl CGI (may convert it to PHP when I get the chance of learning PHP), using a MySQL database, and I'd like some advice on how to set out the database. My question is: should I have a seperate table for each forum, or should I have all messages for all forums in one table, and have a column for 'Forum Name' and query out each forum as needed?
Cheers,
Rikki
Cheers,
Rikki