#1
  1. I am still learning
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    Kuala Lumpur, Malaysia
    Posts
    249
    Rep Power
    17

    Opinion needed on database structure


    I have a situation here where I wish to share with you all.

    I am currently in the midst of creating a database design for a system that will be used by many users. Each users will eventually need about 10 tables in order for the system to work.

    Now, the thing is, what is the best database design to solve this load? User can range from 10 - 100.

    My idea include:

    A) Have seperate database for each individule user
    Pro: Easy to seperate user and easy to backup user's data.
    Con: Difficult to centralized the system. Trouble shooting can be a headache (eg. editing table structure).

    B) Have one database with seperate tables for each individule user
    Pro: Easy to seperate user and easy to backup user's data. One database to maintain instead of many.
    Con: Trouble shooting can be difficult (eg. editing table structure).

    C) Shared database and tables. User are identified with userid column
    Pro: To maintain. Centralized database design. Easy to trouble shoot.
    Con: Hard to backup user's data. Table size can grow very big.

    What do you guys think? Please feedback.

    Many thanks in advanced.
    I have yet to find the right way to do it.

    www.genedavinci.com
  2. #2
  3. I am still learning
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    Kuala Lumpur, Malaysia
    Posts
    249
    Rep Power
    17
    is it better to have many tables or many databases? how many databases can mysql have? -and how many tables can a database handle? i've read somewhere that a database can handle anywhere from 10,000 - 50,000 without any problem...

    advices are greatly appreciated.

    many thanks in advanced
    I have yet to find the right way to do it.

    www.genedavinci.com
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Posts
    9
    Rep Power
    0
    Regardless of how many users you will, just create one database and then create tables. One table will be used for your users and you should have something unique to identify them (e.g. userID or something like that).

    Create as many tables as you will need for them to access and relate each table by the primary key (e.g. userID).

    This is a simplified view of what you should do... database design is big topic for a forum. However, thinking along the lines of what I am suggesting will be better for you than creating a db for each user. That approach will not be efficient and is not scalable.

    If you're saying that you will have between 10 to 100 users and each will need to have access to 10 tables... well, you're still well within the boundaries of what MySQL can support.

    Hope this helped.

IMN logo majestic logo threadwatch logo seochat tools logo