Should I store user id's and profile data (10-15 columns) separately?

Question

I am using PHP and MyISAM, currently I have a profile page for users and am unsure of how I should exactly lay out the tables.

I have tables for users (and every bit of their profile including a pair of MEDIUMTEXT fields for their long description or custom stylesheet,) a table for friends (userid+friendid) and of course a table for comments that may have been placed on their page.

Would it be wise to separate the profile/extra user data from the user table and purely use it for checking with the other tables?

I am unsure if many/or some long fields in each of the rows would be inappropriate if I were just checking for their friends to display or something on another page.

Can you post your table schema, and what exactly you want to separate? — , Jun 28 '11 at 08:47
Possible Duplicate: http://stackoverflow.com/q/6418154/367456 — hakre, Jun 28 '11 at 08:49

score 2 · Answer 1 · answered Jun 28 '11 at 08:48

2

Basically your questions can not be really answered as it's very subjective, so this is more or less some opinion to chime in:

Would it be wise to separate the profile/extra user data from the user column and purely use it for checking with the other tables?

It seems so, because you call it extra user data. So it does not belong to user and should be kept apart of it.

I am unsure if many/or some long fields in each of the rows would be inappropriate if I were just checking for their friends to display or something on another page.

That's hard to answer as well with the information you provide. If you've only doing SELECT * FROM TABLE; queries however, then it would be definitely inappropriate.

answered Jun 28 '11 at 08:48

hakre

193,403
52
435
836

I apologize, I was just wondering if I were going to have a substantial amount of columns if I were just requiring the user ID in the table, if it would be appropriate to have all that data there. – Den R. Jun 28 '11 at 08:50
Do you want to achieve true normalization? What is your goal? – hakre Jun 28 '11 at 08:51
Normalization would be important to me, however it seems I really need to read more about how I should define the relationships in my application before I go one way or the other here (even if it is correct,) I think I asked an impossible question, I apologize. – Den R. Jun 28 '11 at 08:54

score 2 · Accepted Answer · answered Jun 28 '11 at 08:59

Short answer is maybe. Seperating the tables will save space because if the field is not used space will not be allocated. Space probably wont be a concern ( depending on the server and userbase ) but joining tables can slow performance down but again this depends on the userbase and the server. I think primary keys indexes are stored in memory so lookups shouldnt be affected by the number of columns / size of a row.

speed / performance ... denormalize

space normalize

this makes more sense to me (and user ids are indeed primary keys) - I have more confidence just going as I should being a simple and small application. — Den R., Jun 28 '11 at 09:25

score 1 · Answer 3 · answered Jun 28 '11 at 08:50

I think it is good to create a separate table for the profile/extra user data. Keep frequently used column in the user table and move other columns in the new user_profile table. Currently you have 10 - 15 columns but in future it may grow to 50+ columns. So it is better to separate now.

HTH,

Should I store user id's and profile data (10-15 columns) separately?

3 Answers3