Does every user have their own database table?

Question

For most major sites that you can preform CRUD operations does each user have their own database table? I would love to see an example of a database structure which multiple users, I'm looking at my wordpress database structure and just can't picture how twitter, tumblr, or even stackoverflow actually runs a database. Is it that easy to create a table for a user dynamically? Thanks in advance.

Answer 1

There are multiple different types of databases, but typically not. More commonly, you would have a single table, but with a field that designates the user ID. In MySQL, you can select the subset of rows for which the user ID matches a specific value, which is kind of like dynamically creating a per-user table.

Answer 2

Why would each user have their own table? A user should simply be an entry in a table. You then use the id of that user to look up other records in other tables.

If you database contained sy 6 tables then each time you added a user youd also have to add the other tables again too.

so now you have 10 users and 60 tables.

Answer 3

There is no need for separate table for each user. But there could be some kind of optimizations involved with database.

user actually represents row of single table. In relation to user there could be other tables for specific purpose. Suppose "Comments" table contains comments by user, and this table can have multiple comments from user.Then this user table has one to many relationship with comments table.

Some of the optimizations I see here are Creating Views (Materialized Views in certin cases),Column Indexing based on query's used, Even Partitioning of Tables based on Dates/user or some other related criteria to application.

Answer 4

The answer to your question is "no". Usually what these sites do is have one table for users where the key of the table is an integer id field, and the attributes are attributes of the user (e.g. name, screen name, password, etc). In SQLite, it looks like this:

create table User (id integer, email string);

Then you have a couple of tables, say, blog_posts and comments. Each of these table contains blog posts and comments from all users. To identify a user, there is a "user_id" field which identifies a row in that table as belonging to a particular user.

create table BlogPosts (id integer, description text, user_id integer);
create table Comments (id integer, description test, blogpost_id integer, user_id integer);

The syntax is similar for other databases. "user_id" ties a blog post to a user and a comment to a user. blogpost_id ties a blog post to a user. i.e. You can associate each user with all of his questions and comments.

You can create one table per user, but then you have to have a mapping of which user connects to which database, and you need to always open new connections to databases for each user.

Answer 5

You cannot do this safely, since parameterized queries cannot take table-names as a parameter.