Database Efficiency - Table Per User VS. Table of Users

Database efficiency - table per user vs. table of users

The database layout should not change when you add more data to it, so the user data should definitely be in one table.

Also:

• Having multiple tables means that you have to create queries dynamically.

• The cached query plan for one table won't be used for any other of the tables.

• Having a lot of data in one table doesn't affect performance much, but having a lot of tables does.

• If you want to add an index to the table to make queries faster, it's a lot easier to do on a single table.

Effect of more tables on database efficiency SQL

You are very wrong. Ideal way should be to have one single table for users. Even for companies like ebay where there are millions of users; the way those users are distributed are like all users starting with A in a single table ... As @Matt mentioned we cannot create tables where x is not a bounded number.

Along with that table you can have a table Messages which will have a column UserName which will be a foreign key pointing to your user table.

Cleanest possible solution can be a bit tricky for maintaining friends list. A workaround solution can be to have a column in User table which will have comma separated ids of Friends ( which are again users in same table ). If you do not want any limits on friends list: You could just create another table Friends having two columns userid, friendId. Both these columns will be foreign keys on User table. Now, query will be simple select * form Friends where userid=<user> . This table might be huge; but that is where index comes into picture. You can create an index on userid column and query results will be super fast even with lots of records.

Which is more efficient: Multiple MySQL tables or one large table?

Multiple tables help in the following ways / cases:

(a) if different people are going to be developing applications involving different tables, it makes sense to split them.

(b) If you want to give different kind of authorities to different people for different part of the data collection, it may be more convenient to split them. (Of course, you can look at defining views and giving authorization on them appropriately).

(c) For moving data to different places, especially during development, it may make sense to use tables resulting in smaller file sizes.

(d) Smaller foot print may give comfort while you develop applications on specific data collection of a single entity.

(e) It is a possibility: what you thought as a single value data may turn out to be really multiple values in future. e.g. credit limit is a single value field as of now. But tomorrow, you may decide to change the values as (date from, date to, credit value). Split tables might come handy now.

My vote would be for multiple tables - with data appropriately split.

Good luck.

Is it more efficient to create multiple tables for different users?

What you are suggesting is essentially partitioning, so I suggest reading the docs about that. It's mainly advantageous when your operations each cover most of one partition (i.e. select all data for one user, or delete all data for one user).

Most use cases, however, are better served by having one properly indexed table. It's a much simpler structure, and can be very performant. If all of your queries are for a single user, then you'll want all of the indexes to start with the userId column, and postgres will use them to efficiently reach only the relevant rows. And if a day comes when you want to query data across multiple users, it will be much easier to do that.

I advise you not to take my word for it, though. Create both structures, generate fake data to fill them up, and see how they behave!

Database efficiency based on number of tables

SQL doesn't work like that. The paradigm is to have tables that store information about groups of entities, such as companies, people, compact discs. Having a table per entity, i.e. one table for every user, doesn't really make sense, and would be very hard to use.

---

Related Topics