I have several message boards, and each board has 2 tables; one for subjects, one for posts.

When I first built this many years ago, we decided to do 2 separate tables for each board; so, 100 boards = 200 tables. Like:

Ford Message Board:
forums_subjects_ford
forum_posts_ford

Chevy Message Board:
forum_subjects_chevy
forum_posts_chevy

Dodge Message Board:
forum_subjects_dodge
forum_posts_dodge

And so on.

This is becoming a major pain, though, and I'm wishing that I had just set up 1 table for subjects with an ENUM column (eg, "forum_name", where "forum_name" would define which board is used).

Each forum_subject has an AUTOINCREMENT key that corresponds to a matching "id" in forum_posts; this is how it knows which posts go with each thread.

I would love to merge the threads, but the problem is that I have a bunch of duplicate keys; forum_subjects_chevy might have an "id" of 54381, but then, so might forum_subjects_ford, forum_subjects_dodge, and so on; all corresponding to a different series of posts (since they're currently in separate tables).

I know that I can use INSERT to copy all of the subjects in to one table, and let AUTOINCREMENT assign new IDs to all of them. But, how would I then go back through the posts tables and change the IDs there to the new IDs?

Or, better yet, is there a way that I can make the subjects "id" to AUTOINCREMENT without being a unique key; maybe, if "id" and "forum_name" combinedwere unique? This would be best if it's possible, so that any bookmarks tocurrent id's would still work.

TIA,

J

On Tuesday, November 29, 2011 10:17:47 PM UTC-5, Jason C wrote:
Update: it DOES look like I can create a PRIMARY on 2 columns. If I understand correctly, doing so allows me to have duplicate IDs, as long as the second column makes that ID unique.

So, I can have:

id: 1234
forum_name: ford

id: 1234
forum_name: chevy

But, it's worth knowing that the AUTO_INCREMENT for subsequent row inserts is going to be based on the highest number in the row, not based on the group. So, if the current highest ID for "ford" is 1234, and the highest ID for "chevy" is 150000, and then I merge the two tables, then the next ID for ford is going to be 150001 (not 1235).

For the record, to ensure that the database runs nice and fast, I'll be using a cron that runs daily (at midnight) to move rows from the "posts" tablethat are more than 90 days old to a separate "archive" table. I don't think there's a way to do this without using a PHP script, but I'm open to suggestions.

Can you guys confirm if I am correct on all of this?

On 11/30/2011 4:45 AM, Jason C wrote:
Autoincrement is based on the table. MySQL has no idea what a "board"
is. But you're going to run into all kinds of problems trying to
maintain your database like this.

First of all, create a table - "board". It will have (at least) two
columns - an id and board name (potentially others like description, etc.).

Now create a table "subjects" and one "posts". "Subjects" will be the
same as before, only with the addition of a "board" column. "Posts"
will be identical to what you had before.

Now create a script which goes through and reads each of the rows in
your old tables and writes them to the new tables. You'll get a new key
for each row; you'll have to use the new "subjects" key in your "posts"
table.

It's a lot more work, but I think it will be worth it in the long run.

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

On Wednesday, November 30, 2011 9:12:52 AM UTC-5, Jerry Stuckle wrote:
Can you give me an idea of the type of problems I should expect? I set up some of my less active features like this awhile back, and haven't had any technical problems, but the message boards are infinitely more active than those.


I'm not sure that I understand, what would be the purpose of the "board" table?

On 11/30/2011 3:07 PM, Jason C wrote:

With your setup you'll need a compound foreign key (board and subject)
in your "posts" table to reference the appropriate "subjects" table.
While not significantly slower, use of a compound key does slow things
down a bit. And since you're deleting older posts, eventually you won't
need the compound key anyway.

Since you're restructuring the database anyway, I think it would be
better to do it properly.

BTY, your auto_increment column will start incrementing wherever you set
it (i.e. ALTER TABLE posts AUTO_INCREMENT = 150000.

Using an enum as the board id means having to change your database
design every time you want to add (or remove) a new board. That's why I
recommend a table to hold valid board ids. And you could even add an
extra column to that table to indicate the entire board is locked, i.e.
you want to keep the posts for historical purposes but not allow new
posts. Just a lot more flexible.

Plus enums in MySQL aren't validated (unless you use STRICT mode);
foreign keys will be if you're using the INNOdb engine.


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