Page 1 of 1
quick mysql theory question
Posted: Sun Jul 18, 2004 11:39 pm
by pinehead18
I'm writing a small forum for my site and currently i have 3 tables.
forums.
topics
threads.
the topics huse the topics and topic subjects to the threads. the forums holds the forum subject and the threads obvously hold the threads.
Do i need the table topics? Or should i just use threads for it and pull the first post in a thread from teh thread db that != a thread to save mysql?
Thank you
Anthony
Posted: Mon Jul 19, 2004 6:26 am
by kettle_drum
No you dont need the topics table as the first post to a topic as you see it is also a thread. You just need to show that it is the first topic by not assigning it a topic_id or something. Imagine:
Code: Select all
CREATE TABLE threads (
id int(20) NOT NULL auto_increment,
title varchar(255) NOT NULL,
content text NOT NULL,
author...
time_posted
topic_id
forum_id
);
Then for the first thread in a new topic you simply leave the topic_id blank, and then for all threads posted to that topic you put the id of the first thread into the topid_id.
Posted: Mon Jul 19, 2004 8:36 am
by liljester
i would use 4 tables, forums, topics, threads, posts. there are usually 4 steps to a general forum, a list of forums, each forum then has its own topics, then each topic has its own threads, then each thread has some posts or messages. if you use your ids properly you wont have a bit of trouble getting all the info you need with a join. however, you could do away with the top layer of the forums if you wanted and just have topics, theads, and posts
TABLE FORUMS
forum_id
title
description
number_of_topics
TABLE TOPICS
topic_id
forum_id
title
description
number_of_threads
TABLE THREADS
thread_id
topic_id
forum_id
title
date_posted
number_of_posts
TABLE POSTS
post_id
thread_id
topic_id
forum_id
user_id
post
Posted: Mon Jul 19, 2004 11:59 am
by pinehead18
Wow, conflicting replies. Thank you for your replies. Which one is more effecient or better to use?
Anybody else have an opinion on this?
Thank you
Anthony
Posted: Mon Jul 19, 2004 12:39 pm
by kettle_drum
Well its best to do it how you want to and the way you feel more comfortable. In my way you dont hold any duplicate data unlike in liljester's method. There is no need to hold the number_of_posts in any table as you can simply do a COUNT(field) to get that value. And then dont all posts belong to a thread? So why do you need to hold topic and forum id in the thread post table - as this data is already held in the thread table.
Posted: Mon Jul 19, 2004 1:14 pm
by McGruff
Posted: Mon Jul 19, 2004 2:45 pm
by liljester
awe crap. they got the rule book out on me =)
indeed you can just do a count(), but i would still use 3 tables instead of 2
Posted: Fri Jul 23, 2004 8:40 am
by fastfingertips
CREATE TABLE threads (
id int(20) NOT NULL auto_increment,
title varchar(255) NOT NULL,
content text NOT NULL,
author...
time_posted
topic_id
forum_id
);
if id is primary key then that not null does not belongs there (you already have auto_increment)

Posted: Fri Jul 23, 2004 9:23 am
by Draco_03
I think the best design possible follow theses simple rulez
Keep redundant data to a
minimum.
A table should ALWAYS represent one subject only (ie. client table would get info ONLY for clients (exemple, shipping date has nothing to do with client info ( phone or adresse is good info.))
The primary key should always define ALL field directly. Meaning client_id define directly every field in client table.
exemple
Code: Select all
a table with theses fields
invoice number
invoice date
custfirst name
custlast name
employeefirst name
employeelast name
empHome phone
primary key it's the invoice number. Then you ask yourself doe's my primary їi]exclusivelyї/i] identify the current value of
inv date ? YES, invoice number will always identify the date it has been created.
custfirst name? YES, inv. number always identify the first name of the customer
cust lastname? YES, (duh) same reason as first name
employee first name ? YES, it does indicate the employee that served this perticular customer
employee last name > YES, same as first name
empl phone ? NO!!!It їi]indirecetlyї/i] identfies the emplyee's name. The currnet value of both employeefname and employeelname exclusivly identify the value of employee home phone. change the employee name and you MUST change the phone number as well.. get it :)
So you get employee phone out of this table, because
1- you should have a table with employee info anyways,
2- the primary key does NOT exclusivly represent the current value of employee home phone.
EDIT : Typo :/
Posted: Fri Jul 23, 2004 2:59 pm
by penguinboy
You could drop the topic table and in the post table have:
Code: Select all
id (primary key)
root (foreign key) // this would be the id of the root post
parent (foreign key) // this would be the id of the parent post
title
author
comment
To select topics your sql would look something like:
And then to select the posts in the topic:
Code: Select all
SELECT * FROM posts WHERE root=$topic_id
Posted: Fri Jul 23, 2004 3:44 pm
by lostboy
Draco_03 wrote:I think the best design possible follow theses simple rulez
Keep redundant data to a
minimum.
A table should ALWAYS represent one subject only (ie. client table would get info ONLY for clients (exemple, shipping date has nothing to do with client info ( phone or adresse is good info.))
The primary key should always define ALL field directly. Meaning client_id define directly every field in client table.
exemple
Code: Select all
a table with theses fields
...
[/quote]
To clarify Draco's comments, there [b]ARE[/b] cases where denormlization may be required. In large systems comprised of many tables (or querys that require many joins) its often quicker and less resource intensive to denormalize the tables to reduce the number of queries/resources. For a large scale RDBMS (like oracle) you can create a view that essentially does this. MySQL does not yet support views, so you 'may' be at times required to merge tables to do the same thing.
The application has to work a little harder to maintain the extra table(s), but the speed of queries make reads a lot quicker.