Number of database tables versus table size_问答_开发者

I am trying to develop a database for a website. This website has registered users and these users can befriend other users. Much like any traditional social networking site. Now my problem is, I have to store the friends list of each every user and also his profile information. Each user has his own profile id. Apart from this each user is allowed to create groups for his friends and there is also a status field that says the status of the friendship between the two users.

Now to do this I thought of two solutions:

Maintain a table named profiles, in which each user has a profile id and his other profile information and have a global friends table, that has his profile id and his friends profile id. And the other fields would describe, the group, the user has chosen for the particular friend and a status for their friendship and so on.
Maintain a table named profiles, in which each user has a profile id and his other profile information like above and have a separate friends table for each user that says something like friends_profileid, and each user has his own friends list which has all other information.

Which of the abo开发者_运维技巧ve two techniques is more appropriate? I am using MySQL Server 5.0 and I also considered using abstract data types and array types, but that's making the front-end implementation more complex and difficult and cumbersome. While the first technique is increasing the number of rows in a single table at a very high rate, the second technique is increasing the number of tables to be proportional to the number of users? What is more advised? More number of tables or more number of rows?

While the second technique decreases the over head of searching in a single table, what is the overhead of storing the schema again and again? What is the best method for this type of a situation?

SQL was designed to work with a variable number of rows over a fixed set of relationships.

Problems with 1-table-per-user (just off the top of my head):

Stuck with a bunch of hard-to-access and hard-to-maintain tables. No simple way to run queries spanning multiple users. Limits on query size to try to "decompose" these tables in a single query. Most SQL/ORM layers do not provide a nice way to handle this.
The queries generated would need to be "dynamic" with different tables substituted in. While this wouldn't "change the shape", it is hard/impossible for a static analyzer to know this (perhaps that would introduce the notion of a "template"? -- more complexity. no thanks!). This applies to verifying if SQL DDL/DQL is valid (if such tools are used), removes the ability to use static-generated ORMs without additional work, and places more load on the query processor/analyzer.
Could/would also generally lead to worse query plans and overall performance as in the general case, each table (say 100k? no thanks!) would need to be treated independently with potentially huge lack of logical locality. You may also be pushing on some upper RDBMS limits (on the other hand, tables with 2 million records are "small").

Conclusion: Please use your RDBMS to its strengths -- the world has run on SQL for decades now (at least 2 ;-) and most [trivial] problems have been solved numerous times.

You definitely do not need to create an additional, per-user "friends" table. The way that makes sense to me more closely resembles your first solution:

The traditional way to do this, I think, would be to use a join table to create a bidirectional, many-to-many mapping between users. The table would have two columns, both of them foreign keys into the primary key of the users table. An entry in the join table (call it user_friends) represents a "friendship" between two users.

Rather than think in terms of tables, think in terms of what your domain objects are, and what the relationships between them look like.

Use this to drive your DB design, and then encapsulate your database structure behind well defined APIs that perform whatever joins are necessary behind the scenes.