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
Database Design of Message Board Tables
Moderator: General Moderators
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
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
- kendall
- Forum Regular
- Posts: 852
- Joined: Tue Jul 30, 2002 10:21 am
- Location: Trinidad, West Indies
- Contact:
Database Design of Message Board Tables
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
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
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.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.
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....
- kendall
- Forum Regular
- Posts: 852
- Joined: Tue Jul 30, 2002 10:21 am
- Location: Trinidad, West Indies
- Contact:
Database Design of Message Board Tables
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
Thanks
Kendall
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
Thanks
Kendall