PHP - MySQL Data Storage Question

[DanShadow]

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?

[chrisbrown]

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]

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]

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]

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]

Ah, thanks!