Unanswered: Large DB Design: Single DB or Multiple DB ?
I want to design a database for a blog hosted project that has many users with huge amount of data (profile, blogs, post, comments , ...) per user.
each users data should be stored in different tables. I think about two solutions for this problem.
the first one is design one DB Schema and create dynamic table for each user (e.g. usernmae_profile, username_blogs, username_posts , ...)
and the second one is to create a separate DB Schema for each user and each schema contains a constant number of table and data for that user only.
I'll be so glad if someone could help me to compare these solutions in term of DB performance, Security, Code Complexity, etc.
Well, a schema cannot be assigned to a tablespace. Individual tables, within a schema can be assigned to a tablespace however...
A database is assigned to a tablespace.
By creating a new database per user, you may specify where each user's data will reside. A different tablespace could be defined for each user, which physically separates the data into different files. You can build a specific template database which you would use as the base for all new users.
This approach gives you the most flexibility, but, it will also require the greatest overhead (apx. 4 MB, minimum, per user.)
You could also use a common database, and common tables, but include a UserID field in each table. Then, create a set of views for each user, which use the UserID to give each user access to their data, and only give the user access to the views, rather than the tables.
You may also want to consider using partitioned tables, partitioning either by userID, or by date. (With recent posts/blogs in fast storage, and older post/blogs in slower storage.)
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert