OSDev.org

df on trail wrote: well, i wouldnt store a guest name in the user table. bleargh!!! bad design.
...
tblMessagePost
ID
lngUserId
lngGuestID

and i'd use a union join across the two tables on an inner joins and bring it all back in a query, or use a stored procedure to return a cursor.

Using a union join on the two tables in effect makes it EXACTLY the same as the design you just called bad. What's bad about it then?

No it isn't the same, the data would be stored in 2 seperate tables instead of one... which is something I too think is better not only for speed but for keeping the data seperate.

It's faster because it won't have to sift through BOTH guests and members for a post and could skip straight to either the guests or members table. Not much of a noticable speed increase on small boards... but with boards with alot of members/guests it's better.

I've got some other questions

How many char's do you think i should limit a post to? What value does this or most other boards use?

How do you carrry out ATOMIC transactions in MySQL? (using commit/rollback ??)

How many char's do you think i should limit a post to?

Well it depends on how large of posts you think will be posted on your board.

As for what other boards use... I usually see text fields... not set values of characters.

Whats wrong with this query? It doesn't work when i try it saying there is an eror near the 'select MAX.....' part.
I can see anything wrong with this nor find anything else in the manual, and I'm going crazy.
'mod_dt' is a TIMESTAMP field.

Sub queries are not supported until MySQL 4.1... most hosts use 4.0 or 3.x.

4.1 is still alpha so I don't see many people or hosts using it.

then how do i select the latest post in a list of messages?

I should have replied to this earlier. Sorry. :-\
Anyway....

Chris Cromer wrote:

Why would i need to load a page with a lot of topics?

On a topic index where it lists the topics...

Like: http://www.mega-tokyo.com/forum/index.php?board=6

That lists all topics in that board... and if it where to use a seperate table for each topic it would cause it to be extremely slow.

@chris: I should have made myself clearer.
What i meant is that i have a separate table which just stores the 'thread_id' and 'topic' of a thread when one is created.
I then use the 'thread_id' to create a new table for the new thread and store all the replies to that thread in this table.
Chris said that this would be extremely slow to display the topics. But wouldn't that be fast when all the thread topics are in a separate table??
Also in the light of this would it be better to create a separate table for each thread? or will this have more drawbacks (such as more overhead) than the other method of stroing all messages in a single table??
Any insights into this would be appreciated.

Err.... I hope my previous post is not overlooked.

That should select the most recent topic.

Well some hosts limit the ammount of tables you can create... so it could be a problem with them.

I don't see making it done like that making a big difference really...

Does that mean that you recommend storing all the posts in one single table? :-\
I can see that this board has about 49,000 posts(right now). Won't it be slow to select the posts of a particluar board and a particular thread in that board with so many posts to select from??

I also just found out that MySQL does not support the referential integrity constraints (ON DELETE CASCADE etc.). How do i ensure that there are no dangling refrences on deletion etc without these checks?? (apart from going to each table and updating each one by one)

Well all boards that I have ever seen store them in 1 table. MySQL is quite optimized for data retreival which is why you won't notice much difference at all. At most it would be a few milliseconds difference... nothing major.

I also just found out that MySQL does not support the referential integrity constraints (ON DELETE CASCADE etc.). How do i ensure that there are no dangling refrences on deletion etc without these checks?? (apart from going to each table and updating each one by one)

Havn't really dealt with that... I just delete them all in each table that has them...

what about the other question? is there any way to do that?

I modified my message after I posted... guess you read it before it was modified.

Chris Cromer wrote: Havn't really dealt with that... I just delete them all in each table that has them...

What happens if something crashes while your halfway deleting one of the tables?

Well the table would have to be repaired because in some cases as it would probably cause the table to become corrupt... that's one downfall with having it stored in seperate tables for topics... the table could become corrupt if it crashes while deleteing a table.

But the transaction in mysql would be so fast(milliseconds) when deleting, that it's highly doubtfull there would be a crash at the same time your deleting something... possible... but unlikely.