Jump to content


Photo

Better to Create a Redundant Column or Make a Second Call to the Database


  • Please log in to reply
2 replies to this topic

#1 ScrollMaster

ScrollMaster
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 17 July 2006 - 10:46 PM

MySql 5, PHP 5
Hello, I am working on a forum system and I have a question to ask. I had tried mysqlfreaks but there forum system is down.

I am working on generating all the posts for a thread.

I have two tables, a table for users and a table for posts.

I would like to know if it would be better to create a column with the userid and username in the post table so I do not have to make a call to the user table and extract that username that matches the userid.

I think that creating a redundant column would be a waste of space in the database but having it there would make the query faster.

So what do I do?
Option 1: Make the Call to the user table to find the user name to save database space
Option 2: Make a Redundant Column in the post table to save query time
Option 3: Someone tells me of a magical way of making a call to both database in one query?
Option 4: I write my code where it can access itself that the user table is so big that to make a call to it everytime for the posts is expensive and It should add that secound column to the post table.

Which Option should I choose?



#2 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 17 July 2006 - 10:59 PM

I think you know the answer to your question. Putting redundant data in your posts table will create all kinds of problems and any speed increase will be negligible.

You can retrieve the post and username in one query with a simple join something like "select posttable.post, usertable.userid from posttable, usertable where posttable.userid=usertable.userid".
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 18 July 2006 - 07:57 AM

Agreed... multiple copies of any single piece of data is almost always bad (no always, though, but it this case, definitely).  Flex the DB to its full potential, use JOINs, and it will all work out.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users