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

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")


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?


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?


Use one database.

Use one table to hold users and one table to hold quotes.

In between those two tables you have a table that contains information to match users to quotes, this table will hold the rating that a user has given a quote.

This simple design will allow you to store a practically unlimited number of quotes, unlimited users, and you will be able to match up each quote to zero or more users and vice versa.

The table in the middle will contain foreign keys to the user and quote tables.

You might find it helpful to review some database design basics, there are plenty of related questions here on stackoverflow.

Start with these...

What is normalisation?

What is important to keep in mind when designing a database

How many fields is 'too many'?

More tables or more columns?

Need Your Help

Change MySQL query from WHERE to LEFT JOIN

mysql join left-join asterisk

I have a query which reports on Asterisk call usage and queue statistics. The query currently uses MySQL WHERE clauses to join the tables and filter the data.

Pandas scatter_matrix - plot categorical variables

python matplotlib pandas

I am looking at the famous Titanic dataset from the Kaggle competition found here:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.