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
Database layout, normalisation and large user profile site d
Moderator: General Moderators
Re: Database layout, normalisation and large user profile site d
Your questions go to the heart of designing any relational database. What is a table? It represents an "entity". So you have to be very clear about what entities you are representing. As you obviously know, one entity is a user. What are the other entities? Images? Then you need a table for them (NOT multiple tables, just one). Most developers, I think, would advise you NOT to store the image data, itself, in database tables, but rather, store the path/filename to the image file. Some of the rest of the items you listed are entities, too, although it partly depends on whether there is the possibility of a one-to-many relationship, in which case it is clearly a different entity than the user. On the other hand, something like "comments" sounds to me like more of an attribute of a user, not a different entity. You will have to consider each of your items and make up your own mind whether it is an attribute of a user (thus, the user can have only one of them) or a separate entity (the user might conceivably have more than one). Attributes are fields in the table of the entity of which they are attributes.
Let's take images, for example. If you say that a user may have more than one image, obviously that's a one-to-many relationship, so you have a table tblImages that stores ALL the images. Each image record will have its own primary key ID and it will also have the ID of the user, the foreign key. You set the value of the foreign key when you store an image, since at that time you will know the ID of the user whose image it is.
I hope that will get you started.
Let's take images, for example. If you say that a user may have more than one image, obviously that's a one-to-many relationship, so you have a table tblImages that stores ALL the images. Each image record will have its own primary key ID and it will also have the ID of the user, the foreign key. You set the value of the foreign key when you store an image, since at that time you will know the ID of the user whose image it is.
I hope that will get you started.