Basic database principles and strategies

[NoUser2U]

Hi,I got a few questions which i couldn't find the answer to by googling. It's not too much about the technical aspect of using databases, it's rather about their implementation, general uses and principles.1) Is there any 'limit' or so for a programmer to keep table size within? For example, facebook has over 500 million users. Are all these users' login informations, personal informations etc all stored in one table (for example the 'users' table)? Is there any disadvantages (for example: performance wise, searching specific table rows) when using 1 table that has a lot of rows??Or should you 'split' tables when you you're dealing with large amounts of data that'll keep exanding over time (like a userbase)? But if you 'split' tables, how do you deal with the relations of that splitted table with other tables?2) Let's say i have a users table and would like to allow users to send private messages to eachother. I assume it is best to store the private messages into an other table rather than in the 'users' table. Let's say each may have a maximum of 100 private messages in his profile and that the site has about 50k-100k users. Is it best to create one table per user for his messages, or store ALL messages from all users in one specific 'messages' table. Also, let's say every one user of the total of 100k has his inbox full, that makes 100x100k = 10000k messages. That is a LOT of rows in one table (at least i think, i'm pretty new to all this website developing and database-usage ). Should the messages-table (incase it is best to use 1 table for all the messages) be split in this situation (like point 1 above regarding large amounts of rows)3) Is it possible to 'share' rows from one table with rows in an other table? For example: users can create multiple notes for theirselves and you have a table called 'notes' which contains ALL notes from all users (and each note is linked to it's user from the 'users' table). But users should be able to share their notes with others, 'sending' their notes to other users, but keeping their notes also for themselves. Is it possible to link these shared notes between multiple other rows from an other table??For the record: i'm using Mysql. More such questions may follow when they come up in my head :)Thank you for reading and much more thanks if you reply!

[birbal]

1) you search partitioning in google or in mysql..you will find lot of links...and brief infopartitioning is the way which split the tables...not sure about facebook likee huge site though..probably they do some kind of load balancing with multiple server..2) i think you should stick with one table..did you think if you make each table for each user for inbox..how many tables will comes up?...3) that could be done by foreign keys..and joins..using foreighn keys i think.

[NoUser2U]

thank you for your replie birbal,about point 2)But what if there are 10000k records (= messages) stored in the 'messages'-table and let's say a users goes to his profile or so to see his messages, won't it take forever for the database to search for the messages to see which belongs to that specific user in order to display them??That's the main thing that bothers me...Any more responses are also welcome .

[birbal]

that is also can make up by the partition..even index..index helps to find row more quickly..putting index in search column (generaly which is used in WHERE clause) can enhance the searching process.most of the sites (which i had used) limit the messages anyhow..some clear out inbox which are older than certain amount of time..some do limit at count...facebook like sites are execptional.

[NoUser2U]

thank you much for your replies birbal, they really clear things out! .I also been reading much on indexes, foreign key's and linking tables together since yesterday and things are getting clearer and clearer, but..Let's say i have 2 tables: users and messages,

CREATE TABLE users (user_id INT,PRIMARY KEY (user_id),);CREATE TABLE messages (mssg_id INT,mssg_content VARCHAR(1500),link INT,PRIMARY KEY (mssg_id),FOREIGN KEY (link) REFERENCES users(user_id));

Now let's say a user is logged in and goes to his messages section to see his messages, how do i select and display his own messages only? Is this when the JOINS come in?

[birbal]

yes you need to link through the foreign keys in join(link will be connected into user_id)...and then can put condition to retreive messages only where user_id mactches the logged use id.

[NoUser2U]

Okay thanks for your input . I think i have enough information as of now to get me going...whenever new questions pop up i'll reply back in this topic.Thanks again