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

Question

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?

Answer 1

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?

Answer 2

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

Neither, you should use one database, with a table for users, a table for quotes, a table for rates, etc.

You then have a column in (e.g.) your quotes table which says which user the quote is for.

CREATE TABLE user (
    user INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ...
);

CREATE TABLE quote (
    quote INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user INT(10) UNSIGNED NOT NULL,
    ...
);

CREATE TABLE rate (
    rate INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user INT(10) UNSIGNED NOT NULL,
    ...
 );

You then use SQL JOINs in your SELECT statements to link the tables together.

EDIT - the above was assuming a many-to-one relationship between users and rates - where there are 'many-to-many' relationships you need a table for each sort of data, and then another table with rows for each User <-> Rate pair.

Answer 3

Two problems with many databases (actually many more, but start with these.)

1. You can't use parameters for database names.

2. What will you do whe you make your first change to a table? (Hint: Work X (# databases) ).

And "many tables" suggests you're thinking about tables per user. That's another equally problematic idea.