Database layout, normalisation and large user profile site d
Posted: Sat May 16, 2009 1:52 pm
Hi all,
I am in the process of making a user signup site with profiles. The users are invited to join, so it’s not an open to the public signup process. They will signup with a username, email, password, URL and a few other normal details.
They will then have there profile information associated with them, so
-images,
-text,
-mp3s,
-links,
-comments,
-profile pic
-etc
So I have a main table that holds username/password/email/url for each user and I am now wondering do I create individual tables for:
-images,
-text,
-mp3s,
-links,
-comments,
-profile pic
-etc
and use an id generated from the username/password table to reference the individuals different elements in the separate tables, or foreign keys with innodb, or do I create a table (on the fly - not sure if I can but then again i would be surprised if I couldn’t with php and mysql) when a user signups up that holds all of there individual information.
Considering all tables are indexed correctly, and also considering that some tables may or may not hold information and considering the normalization practices I have learnt I think I may of answered my own question, go with individual tables? As that would be better than one massive table?
The thing that I am concerned about is say I have 1,000 users, they all upload 10 mp3's of there own music, so I then have 10,000 mp3 urls in the mp3 table, surely with all these profiles say being viewed by say 100 people at the same time this is going to cause big problems? (Am I underestimating mysql again?) but then what if its 10,000 users...
The site isn’t going to be large but that’s not really the point. I am interested in making it work as well as it can, so for instance even if there was 40,000 users all with full profiles Id like the table layout to work for that as well as possible.
Anyway - to the thing I love about Dev forums can I tap into your thoughts, experience and knowledge please as I can’t seem to find them in my mysql books!
Id love to know if anyone has an idea how the large sites (facebook, linked-in, match) holding individuals data, and how there database schema looks or indeed have examples, links to information explaining more about database construction.
As always thanks in advance for any information/help
I am in the process of making a user signup site with profiles. The users are invited to join, so it’s not an open to the public signup process. They will signup with a username, email, password, URL and a few other normal details.
They will then have there profile information associated with them, so
-images,
-text,
-mp3s,
-links,
-comments,
-profile pic
-etc
So I have a main table that holds username/password/email/url for each user and I am now wondering do I create individual tables for:
-images,
-text,
-mp3s,
-links,
-comments,
-profile pic
-etc
and use an id generated from the username/password table to reference the individuals different elements in the separate tables, or foreign keys with innodb, or do I create a table (on the fly - not sure if I can but then again i would be surprised if I couldn’t with php and mysql) when a user signups up that holds all of there individual information.
Considering all tables are indexed correctly, and also considering that some tables may or may not hold information and considering the normalization practices I have learnt I think I may of answered my own question, go with individual tables? As that would be better than one massive table?
The thing that I am concerned about is say I have 1,000 users, they all upload 10 mp3's of there own music, so I then have 10,000 mp3 urls in the mp3 table, surely with all these profiles say being viewed by say 100 people at the same time this is going to cause big problems? (Am I underestimating mysql again?) but then what if its 10,000 users...
The site isn’t going to be large but that’s not really the point. I am interested in making it work as well as it can, so for instance even if there was 40,000 users all with full profiles Id like the table layout to work for that as well as possible.
Anyway - to the thing I love about Dev forums can I tap into your thoughts, experience and knowledge please as I can’t seem to find them in my mysql books!
Id love to know if anyone has an idea how the large sites (facebook, linked-in, match) holding individuals data, and how there database schema looks or indeed have examples, links to information explaining more about database construction.
As always thanks in advance for any information/help