Programming C, C++, Java, PHP, Ruby, Turing, VB
Computer Science Canada 
Programming C, C++, Java, PHP, Ruby, Turing, VB  

Username:   Password: 
 RegisterRegister   
 PHP - MySQL Data Storage Question
Index -> Programming, PHP -> PHP Help
View previous topic Printable versionDownload TopicSubscribe to this topicPrivate MessagesRefresh page View next topic
Author Message
DanShadow




PostPosted: Thu Oct 14, 2010 8:56 pm   Post subject: PHP - MySQL Data Storage Question

This question is more about MySQL Databases.

I'm storing data that references 2 or more tables using foreign&primary keys.

One table is a "user" table, one table is a "message" table.
The "message" table uses a 'userid' foreign key to reference the 'userid' primary key of the "user" table.

My question, is would it be faster to store information from the "user" table (ie. name) in the "message" table, and only do 1 SELECT statement from the "message" table, or faster to do 2 SELECT statements, one to get the FK "userid" from the "message" table, and the second to get the users name from the "user" table?

In school I was always told to eliminate redundant table data, so im assuming the latter is a better idea.. but my concern is speed.

Any suggestions?
Sponsor
Sponsor
Sponsor
sponsor
chrisbrown




PostPosted: Thu Oct 14, 2010 9:21 pm   Post subject: Re: PHP - MySQL Data Storage Question

Good database architecture always trumps (and often prevents the need for) performance considerations. If you want to store the name of a user, that name belong in the user table.

In this case, look into joins. They allow you to do exactly what you want to do: in one select statement, you can get columns from multiple tables based on some condition, e.g
code:
SELECT *
  FROM user INNER JOIN messages
    ON user.id = messages.userid;


or using an implicit join:
code:
SELECT *
  FROM user, messages
  WHERE user.id = messages.userid;


Examples shamelessly lifted from http://en.wikipedia.org/wiki/Join_%28SQL%29
DanShadow




PostPosted: Thu Oct 14, 2010 9:33 pm   Post subject: RE:PHP - MySQL Data Storage Question

Joins.. interesting, I was never taught those in my DB courses, lol.

Im guessing you could still use $row['FirstName'] and $row['Message'] (two different table columns) by joining two tables?

If so, thanks!
That will make things helpful, as the design for a side project im working on has 11 tables so far, 3 of which reference tables containing similar table information, but the need for multi-value same-column storage has forced me to create another table.
DanShadow




PostPosted: Thu Oct 14, 2010 9:52 pm   Post subject: RE:PHP - MySQL Data Storage Question

Hm, I have another question.

I was curious about variable storage size, does anybody know how it affects a Database (besides taking up extra memory)?

I've been trying to consider what size I should make some of the VARCHAR's, and I don't want to limit the user's input too much.
For example, user passwords will be stored as SHA1 (+key) hashes, which increases password length. I dont want to make the size too small, so I was wondering if too large poses a noteable problem?
chrisbrown




PostPosted: Fri Oct 15, 2010 2:10 am   Post subject: Re: RE:PHP - MySQL Data Storage Question

DanShadow @ Thu Oct 14, 2010 9:33 pm wrote:
Im guessing you could still use $row['FirstName'] and $row['Message'] (two different table columns) by joining two tables?

Yes, the result set is a single table with columns from both of the joined ones.

Regarding your second question, you probably won't notice a difference if you are somewhat generous with your sizing.
Just a heads up, a SHA1 hash is always 80 characters long.
DanShadow




PostPosted: Fri Oct 15, 2010 7:31 am   Post subject: RE:PHP - MySQL Data Storage Question

Ah, thanks!
Display posts from previous:   
   Index -> Programming, PHP -> PHP Help
View previous topic Tell A FriendPrintable versionDownload TopicSubscribe to this topicPrivate MessagesRefresh page View next topic

Page 1 of 1  [ 6 Posts ]
Jump to:   


Style:  
Search: