MySql implementing Multiple databases or tablespaces like Oracle for performance enhancement

I am in the process of designing a Database structure for a project. And i am stuck at a particular design decision. Ive searched quite a lot on google but not able to find a satisfactory answer.

I am looking out for something like “TableSpaces” (like in Oracle) to use in MySql. I have provided a sample table structure below for better understanding of what i want to do.

Table Structure
• CommonRepository  (This table contains only a list of Items and their descriptions)
o ItemId, name, picture, description, dimension1, dimension2, dimension3, dimension4.
• Users  (This table contains list of users with their details)
o UserId, FirstName, LastName, Address.
• AllUserItems  (This links each User to his/her list of Items)
o UserId, ItemId
• UsersRepository (this is a Joined table/view of “CommonRepository” & “AllUserItems” hence will show Items only for 1 specific user)

Ideally i would like to have 1 tablespace(oracle style) for each user. tableSpace types are given below
• AdminTableSpace  Contains the below tables in it
o CommonRepository
o Users
o AllUserItems
• UserTableSpaces  There are many such tableSpaces, one for each user
o UserRepository (This is a table if it is used in tableSpaces else if no tableSpaces are used then this is a View)

Records from one user is independent of another user. Except when admin wants to generate reports of all users performance. This would be very frequent.

Please note that i am expecting about 100 to 1000 users (not less) and atleast 1000 to 10,000 Items per user.

Each user will have different accounts and their Items are no way related to other users Items. Hence when each user is searching for his Items for modifications, If he is querying a Table with only his data it would be much faster. This can be implemented in Oracle with tableSpaces (As far as i know, correct me if in wrong). But from what ive read. MySql Does not have TableSpaces. So thought of using 1 database for each user. Now considering that there might 100 or 10000 of users making a database for each user does not seem practical. Also many online servers may not provide so many MySqlDatabases to a single account.

What design structure should i use, i have enlisted a few that i can think of, please suggest what would be the best method to implement my design, also please explain where im going wrong in my design.

  1. Have 1 database, all tables in one database and use views for each user. (this might slow things down drastically for every user)
  2. Have 1 database but multiple tables for each user. (is this a bad design ?)
  3. Have multiple databases 1 for each user and use data by using “DatabaseName.TableName” (This does not seem very practical if number of users are large, also ive read that some joins and queries may not work correctly for this method).

Please suggest what approach i could use.

[B]i.connect wrote on Wed, 03 November 2010 06:07[/B]

Hence when each user is searching for his Items for modifications, If he is querying a Table with only his data it would be much faster.

Not necessarily, when making a statement like that you are not thinking about all other concurrent queries from other users that are taking place at the same time that competes about the resources. And finding/opening the correct table also takes time plus system resources compared to finding/reading the correct record in one table. A database is designed to handle millions of rows in one table and not 10 rows in 100,000 tables.

And especially since you say:

[B]i.connect wrote on Wed, 03 November 2010 06:07[/B]

Except when admin wants to generate reports of all users performance. This would be very frequent.

Which sounds like this is actually _one_ application that you frequently want to draw reports from. Generating good reports from a design where related data has been split up into a lot of tables gets very complex. Especially if you talk about a couple of tables per user which means that the design of the database isn't the same (see 2 below).
[B]i.connect wrote on Wed, 03 November 2010 06:07[/B]
  1. Have 1 database, all tables in one database and use views for each user. (this might slow things down drastically for every user)
This is the recommended design. With proper indexes on the tables finding a certain users records shouldn't be a problem. And you shouldn't use one view per user (which it sounds a bit like you where thinking) you should build into your application layer (or stored procedure or something) that if you are user xyz then the queries will always look something like "WHERE user = 'xyz'" (or actually the userid that that user has but that is another story).
[B]i.connect wrote on Wed, 03 November 2010 06:07[/B]
  1. Have 1 database but multiple tables for each user. (is this a bad design ?)
Yes it is, try generating a report from that, and especially if you get a new user you have to create a new table and removing a user means drop table, etc.
[B]i.connect wrote on Wed, 03 November 2010 06:07[/B]
  1. Have multiple databases 1 for each user and use data by using “DatabaseName.TableName” (This does not seem very practical if number of users are large, also ive read that some joins and queries may not work correctly for this method).
Even more complex than nr 2 and having a lot of databases is even worse than just having a lot of tables.

I’ve written the above recommendation based on that it sounds like this is in fact one application, it’s just that you have a lot of users of it.
If data has the same properties it should 99 times out of 100 be placed in only table. The only time when you should partition data into more than one table is when you are running into performance problem with underlying storage and at that point you should split that data based on the nr of storage devices that you have and not on how many users you are servicing.

Summary: choose 1, because if it is the same application you shouldn’t split the data over several databases. And you shouldn’t design your database so that the design needs to be changed on the fly based on the data that is stored in it.
A database design should be fixed during creation and then you should only insert/update/delete data from it.