Impact of More Tables on SQL Database Performance
I am currently trying to create chat program
for 1000-1500 users. I was wondering if I should create a separate table for each user
, like in the table for each user of friends, posts, or just create one, include the whole table and save everything there, like in which solution would there be more efficient
? I believe there would be tables multiple
for every user's personal information more efficient
, so most of the queries I run run on tables with little data.
Can anyone tell me if I am wrong?
source to share
You are very wrong. The ideal way should be to have one table for users. Even for companies like ebay with millions of users; how are these users distributed, like all users starting with A in the same table ... As @Matt pointed out, we cannot create tables where x is not a limited number.
Along with this table, you can have a Messages table that will have a UserName column which will be a foreign key pointing to your user table.
The cleanest possible solution can be a little tricky to maintain your friends list. A workaround could be a column in the User table that will have comma separated IDs of friends (who are again users in the same table). If you don't want any restrictions on your friends list: you could just create another Friends table that has two userid, friendId columns. Both of these columns will be foreign keys in the User table. The query will now be simple select * form Friends where userid=<user>
. This table can be huge; but this is where the index comes into picture. You can create an index on the userid column and the query results will be very fast even with a large number of records.
source to share
Bad approach to setting up the table for each user, you can exactly save a text file for each user, you don't need a database for that, the idea of using ddatabase is to create relationships between entities, have primary / foreign keys, in general you need a table for each object, sometimes 3 tables for 2 entities, depending on the cardinality. In your case, it would be ok to have, for example, a user table, a message table, a messageUser table, and a freinds or contacts table, etc., where freinds stores user ids connected as freinds, and messages are one of the messages Id ↔ UserId, which matches messages to the user, etc.
source to share