quick mysql theory question

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
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

quick mysql theory question

Post 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
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post 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
pinehead18
Forum Contributor
Posts: 329
Joined: Thu Jul 31, 2003 9:20 pm

Post 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
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post 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.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

User avatar
liljester
Forum Contributor
Posts: 400
Joined: Tue May 20, 2003 4:49 pm

Post 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
fastfingertips
Forum Contributor
Posts: 242
Joined: Sun Dec 28, 2003 1:40 am
Contact:

Post 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) :D
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post 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 :/
penguinboy
Forum Contributor
Posts: 171
Joined: Thu Nov 07, 2002 11:25 am

Post 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:

Code: Select all

SELECT * FROM posts WHERE root=0
And then to select the posts in the topic:

Code: Select all

SELECT * FROM posts WHERE root=$topic_id
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post 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.
Post Reply