Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2008

    Question 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.

  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    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

  3. #3
    Join Date
    Oct 2008

    Thumbs up

    tnx for your reply. it was very useful

  4. #4
    Join Date
    Nov 2008
    Currently studying in France but not French ;)
    hey milad, looks like you're doing a big project, that's cool.
    I'm just starting SQL and loving it.
    anyway figured your name was persian, just wanted to say hi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts