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

Neither. You create a single database with a single table for each type of data, then use foreign keys to link the data for each user together. If you had only a few, fixed users, this wouldn't be so bad, but what you're suggesting is simply not at all scalable.

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.

Answer 4

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 5

Your design is flawed.. You should constrain the tuples using foreign keys to the correct user, rather than adding new entities for each account.

Answer 6

we've got a similar system, having many users and their relevant datas. We've followed a single database and common tables approach. This way you would have a single table holding user information and a table holding all their data. Along with the data we have a reference to the userid which helps us segregate the information.

Answer 7

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.

Answer 8

Use one database and one table. You will require a table "User" and this table will be linked (Primary--Foreign key) to these table.

Answer 9

For comparison, a time when you actually do want separate databases would be when you have multiple webhosting clients that want to do their own things with the database - then you can set up security so they can access only their own data.

But if you are writing the code to interface with the data base, not them, then what you want is normalized tables as described in several other answers here.

Answer 10

One table with properly created indexes per each required entity set (one table for submitted quotes, one table for submitted rates).

CREATE TABLE quotesSubmtited (
   userid INTEGER, 
   submittime DATETIME, 
   quote INTEGER,
   quotedata INTEGER, 
   PRIMARY KEY (userid, submittime),
   FOREIGN KEY quote REFERENCES quotesList (quoteId),
   FOREIGN KEY userid REFERENCES userList (userId)
);

CREATE INDEX idx1 ON quotesSubmitted (quote);

Remember: more indexes you create, slower the updating. So take a closer look at what you use in queries and create indexes for that. A good database optimization tutorial will be of invaluable help in understanding what indexes you need to create (I cannot summarize it in this answer).

I also presume you don't know about JOINs and FOREIGN KEYs so make sure you read about them as well. Quite useful!