Alex Rivera | Logout

Is it unreasonable to assign a MySQL database to each user on my site?

Asked 2008-11-29T18:08:06.473
10

I'm creating a user-based website. For each user, I'll need a few MySQL tables to store different types of information (that is, userInfo, quotesSubmitted, and ratesSubmitted). Is it a better idea to:

a) Create one database for the site (that is, "mySite") and then hundreds or thousands of tables inside this (that is, "userInfo_bob", "quotessubmitted_bob", "userInfo_shelly", and"quotesSubmitted_shelly")

or

b) Create hundreds or thousands of databases (that is, "Bob", "Shelly", etc.) and only a couple tables per database (that is, Inside of "Bob": userInfo, quotesSubmitted, ratesSubmitted, etc.)

Should I use one database, and many tables in that database, or many databases and few tables per database?

Edit:

The problem is that I need to keep track of who has rated what. That means if a user has rated 300 quotes, I need to be able to know exactly which quotes the user has rated.

Maybe I should do this?

One table for quotes. One table to list users. One table to document ALL ratings that have been made (that is, Three columns: User, Quote, rating). That seems reasonable. Is there any problem with that?

mysql database
Edit
Report

1 Answer

0

What is the difference between many tables in one database or many databases with same tables? Is it for better security or for different types of backups?

I m not sure about mySQL but in MSSQL it is like this:

  • If you need to backup databases in different way you need to consider keeping tables in different data files. By default they all are in PRIMARY file. You can specify different storage.

  • All transactions are hold in tempdb. This is not very good because if it transaction log becomes full then all databases stop functioning. Then you can end up with separate SQL servers for each user. Which is sort of nonsense if you are talking about thousands of clients.

answered 2008-11-29T18:16:55.270

Your Answer