Database Design of Message Board Tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Database Design of Message Board Tables

Post by kendall »

Hello,

I need some advice concerning creating an effecient database tables for a message board.

I have designed my database in a way that the body messages are in a separate table from the rest of the message information relationed only by id

Now in trying to develop how to add new messages i realised that i can run into a problem with it but there are work arounds

my question is...is it wise to have the body message in a separate table from the rest of the message information.?

Kendall
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

If it is wise or not all depends on the rest of your application and db design, I am no expert but I have played with a few things..

Usually, if you get a 1 to 1 relationship, splitting a table is against normalization.. But, in some circumstances you may do de-normalization to increase speed on certain queries.

If your search function is built with separate keyword index tables it is not of much use to split it up, if you are doing searches that will require full table scan it may be useful to split up, but likely it is better to add a full-text index instead.. easiest way to find out is creating your application in a few layers so you can easily test and change...

A bb usually has way more reads than writes, so indexing the hell out of it may be useful. Don't do (massive) joins when searching, find the id's and join/put together in another query :)
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Database Design of Message Board Tables

Post by kendall »

Ahh,

ok the problem i thought i might be facing would be when inserting the post info the id is automatically generated. so i thought once the inserting is done simaltaneous i can guess the id and manually insert one into the message table rather than do an insert then search to confirm an id to then be inserted into the message part. the search to confirm part i thought mite leave room for error. Then i thought...what is someone was posting wile another post was being inserted...then guessing would leave room for error.

Given that my searches are going to be id related i think i will not split the information into 2 tables.

Thanks for the advice

Kendall
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Stoker wrote: Usually, if you get a 1 to 1 relationship, splitting a table is against normalization.. But, in some circumstances you may do de-normalization to increase speed on certain queries.
Hmm, I can't let this stand. Spliting a given table into two 1:1 tables is NOT "against normlization". It might not be dictated by normlization rules, but its not prohibited. Whether its dictated or not will depend on the other fields in the table, and what the functional dependencies are.

I would strongly argue against denormalization. I've never seen a case where the "increased performence":
1) actually materilizes
2) doesn't require massive amounts of application code to protect the data's integrity

If you're worried about optimization, some things to think about is size of tables and availible RAM. Smaller tables are more likely to be kept in RAM. For that reason alone I would probably move the full text message body into a table of its own. Now searching titles/authors of post can be very quick. Yes you will need a join to reassmeble a thread....
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Database Design of Message Board Tables

Post by kendall »

Eric,

Hmm...ok...don't think he meant any harm in what he was saying. I have chosen to Keep everything in one table as i find it a bit tideous in coding when adding a new post. My message board will not be as apt as this one is. My Database queries will more or less stick to specific data columns which are indexed. so its no biggie :wink:

Thanks

Kendall
Post Reply