quick mysql theory question
Moderator: General Moderators
-
pinehead18
- Forum Contributor
- Posts: 329
- Joined: Thu Jul 31, 2003 9:20 pm
quick mysql theory question
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
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
-
kettle_drum
- DevNet Resident
- Posts: 1150
- Joined: Sun Jul 20, 2003 9:25 pm
- Location: West Yorkshire, England
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:
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.
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
);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
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
-
pinehead18
- Forum Contributor
- Posts: 329
- Joined: Thu Jul 31, 2003 9:20 pm
-
kettle_drum
- DevNet Resident
- Posts: 1150
- Joined: Sun Jul 20, 2003 9:25 pm
- Location: West Yorkshire, England
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.
Essential reading: relational database architecture.
-
fastfingertips
- Forum Contributor
- Posts: 242
- Joined: Sun Dec 28, 2003 1:40 am
- Contact:
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
EDIT : Typo :/
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.-
penguinboy
- Forum Contributor
- Posts: 171
- Joined: Thu Nov 07, 2002 11:25 am
You could drop the topic table and in the post table have:
To select topics your sql would look something like:
And then to select the posts in the topic:
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
commentCode: Select all
SELECT * FROM posts WHERE root=0Code: Select all
SELECT * FROM posts WHERE root=$topic_idDraco_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.
exempleCode: 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.