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.