Structure of MySQL tables for message board

I have a message board that currently uses flat text files, and I'm
wanting to convert it to MySQL. The program that accesses the database
will be Perl, if it matters. I've written the program that will move
all of the data over, but I have one seemingly minor stumbling block.

My intention was to have one table for each topic, and the rows of the
table would represent each post. The fields, in order, would be:

timestamp, username, email, post

What I DON'T have, though, is a way to timestamp the table itself. So,
how do I sort the tables / threads by the most recent post?

Keep in mind, there are currently about 17,000 threads, and growing at
a current rate of about 10,000/year. So, loading every table into an
array and sorting by the timestamp field would be very slow and
inefficient.

My original idea was to use a separate flat-text database to hold all
of the subject names and last timestamps, and then just use MySQL to
hold the threads and cross-reference them. This is how the program is
currently running, but I'm sure that there must be a better way.

TIA,

Jason

On 27 Jul, 08:24, Jason <jwcarl...@gmail.com> wrote:
> I have a message board that currently uses flat text files, and I'm
> wanting to convert it to MySQL. The program that accesses the database
> will be Perl, if it matters. I've written the program that will move
> all of the data over, but I have one seemingly minor stumbling block.
>
> My intention was to have one table for each topic, and the rows of the
> table would represent each post. The fields, in order, would be:
>
> timestamp, username, email, post
>
> What I DON'T have, though, is a way to timestamp the table itself. So,
> how do I sort the tables / threads by the most recent post?
>
> Keep in mind, there are currently about 17,000 threads, and growing at
> a current rate of about 10,000/year. So, loading every table into an
> array and sorting by the timestamp field would be very slow and
> inefficient.
>
> My original idea was to use a separate flat-text database to hold all
> of the subject names and last timestamps, and then just use MySQL to
> hold the threads and cross-reference them. This is how the program is
> currently running, but I'm sure that there must be a better way.
>
> TIA,
>
> Jason

"One table for each topic"? Is that how it's normally done?

Message-ID: <1185521040.589035.32780@d55g2000hsg.googlegroups. com> from
Jason contained the following:

>My original idea was to use a separate flat-text database to hold all
>of the subject names and last timestamps, and then just use MySQL to
>hold the threads and cross-reference them. This is how the program is
>currently running, but I'm sure that there must be a better way.

Two tables, one for messages, the other for threads should work, unless
I'm missing something.

--
Regards,

Geoff Berrow

On 27 Jul, 08:24, Jason <jwcarl...@gmail.com> wrote:
> I have a message board that currently uses flat text files, and I'm
> wanting to convert it to MySQL. The program that accesses the database
> will be Perl, if it matters. I've written the program that will move
> all of the data over, but I have one seemingly minor stumbling block.
>
> My intention was to have one table for each topic, and the rows of the
> table would represent each post. The fields, in order, would be:
>
> timestamp, username, email, post
>
> What I DON'T have, though, is a way to timestamp the table itself. So,
> how do I sort the tables / threads by the most recent post?
>
> Keep in mind, there are currently about 17,000 threads, and growing at
> a current rate of about 10,000/year. So, loading every table into an
> array and sorting by the timestamp field would be very slow and
> inefficient.
>
> My original idea was to use a separate flat-text database to hold all
> of the subject names and last timestamps, and then just use MySQL to
> hold the threads and cross-reference them. This is how the program is
> currently running, but I'm sure that there must be a better way.
>
> TIA,
>
> Jason

One of the basic tenets of good programming is re-usability.

There are so many good, and free, message board programs out there,
why are you looking to write another (especially with your obvious
lack of uderstanding of database design)?

I am an experienced programmer and I wouldn't bother to write a new
one from scratch!

> Two tables, one for messages, the other for threads should work, unless
> I'm missing something.

My biggest concern at the moment is speed, and considering that there
are roughly 600,000 posts to transfer, I didn't think that having them
all in one table would be very efficient.

Is that wrong?

Assuming so (else you wouldn't have suggested it), how would I
structure the table with the threads?

- Jason

> One of the basic tenets of good programming is re-usability.
>
> There are so many good, and free, message board programs out there,
> why are you looking to write another (especially with your obvious
> lack of uderstanding of database design)?
>
> I am an experienced programmer and I wouldn't bother to write a new
> one from scratch!


Don't misunderstand me; I've been a programmer since 1995, just rarely
have the need to use a database other than flat text.

The site that this is for is one that I originally built in '99 (along
with the message board), and have expanded upon since. An OOB wouldn't
work, because the program is heavily integrated into so many features
of the site that it would require a complete rewrite, anyway.

But I'm not talking about writing a message board; I already wrote one
that's working great. I'm just talking about modifying it slightly in
the hopes of making it run faster and more efficiently with a better
database.

- J

On Jul 27, 4:46 am, Jason <jwcarl...@gmail.com> wrote:
> > Two tables, one for messages, the other for threads should work, unless
> > I'm missing something.
>
> My biggest concern at the moment is speed, and considering that there
> are roughly 600,000 posts to transfer, I didn't think that having them
> all in one table would be very efficient.
>
> Is that wrong?
>
> Assuming so (else you wouldn't have suggested it), how would I
> structure the table with the threads?
>
> - Jason

Duh, it's late/early, forgive me.

I'm guessing that this would be SUBJECT, TIMESTAMP, USERNAME, EMAIL,
POST, and then just query the table on the SUBJECT field. My only
concern was speed, but I'm reading in the MySQL documentation that
speed shouldn't be an issue.

I'll play with it like that unless you tell me I'm screwing up
royally.

- J

Jason wrote:
> On Jul 27, 4:46 am, Jason <jwcarl...@gmail.com> wrote:
>>> Two tables, one for messages, the other for threads should work, unless
>>> I'm missing something.
>> My biggest concern at the moment is speed, and considering that there
>> are roughly 600,000 posts to transfer, I didn't think that having them
>> all in one table would be very efficient.
>>
>> Is that wrong?
>>
>> Assuming so (else you wouldn't have suggested it), how would I
>> structure the table with the threads?
>>
>> - Jason
>
> Duh, it's late/early, forgive me.
>
> I'm guessing that this would be SUBJECT, TIMESTAMP, USERNAME, EMAIL,
> POST, and then just query the table on the SUBJECT field. My only
> concern was speed, but I'm reading in the MySQL documentation that
> speed shouldn't be an issue.
>
> I'll play with it like that unless you tell me I'm screwing up
> royally.
>
> - J
>

600K messages is nothing for any decent database.

You will want a primary key, also - probably an autoincrement column.
Also, you'll want a separate table for users, keeping the userid only in
this table. Probably the same with subjects. Then just join the tables
in your query. If possible, use integers instead of strings in your
joins - they are more efficient.

Before you get too far, Google for "database normalization". It will
help you design your database.

Also, don't prematurely optimize. Implement a solid solution. Then if
you have performance problems, determine the cause and correct it.

But don't worry about performance problems until they occur. Doing so
will just give you ulcers and turn your hair gray.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Message-ID: <1185531570.625486.39810@d55g2000hsg.googlegroups. com> from
Jason contained the following:

>I'm guessing that this would be SUBJECT, TIMESTAMP, USERNAME, EMAIL,
>POST, and then just query the table on the SUBJECT field.


Put very simply I think it would be something like this

tbl_messages
message_id, thread_id, user_id,timestamp, message
tbl_threads
thread_id,subject,status,...
tbl_users
user_id,email,name,...

More complicated if you are doing more complex threading, or
categorising threads.


>My only
>concern was speed, but I'm reading in the MySQL documentation that
>speed shouldn't be an issue.


Others will have more experience of dealing with large databases than
me, but from what I've read and seen you are right, speed should not be
an issue provided that data is properly structured and indexed.

I'd recommend drawing an entity relationship diagram and ensuring the
database is properly normalised before writing a mass of code.


--
Regards,

Geoff Berrow