#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    3
    Rep Power
    0
    Hi everyone,
    Me and a couple of other people will be volunteering a project for four universities in php and mysql. I have some questions conerning design issues (for example which is fast, takes less space etc)
    The design will have to manage 3,000 users possibly up to 80,000. Each user will have an account they must login to where they can store their own demographic information. They can login through the web and change this information anytime These 3,000 people though will also belong to 6 different groups. some of the members (perhaps 10%) will be members of more then one group while the rest will belong to one group only. Each group will use it's own user defined variables for it's members. So each member has their demographic information (this would be generic for all the users) plus any variables they must fill out for the group(s) they belong to. As far as the design goes, I'm wondering if it's faster to (assume the long/passw table etc):

    A. have one table with all the users' generic demographic information and then individual tables for each group, with the user defined variables. Therefore when somone logins in it would query the main table for the person's demographic information, and then query the appropriate group(s) tables the user belongs to for the person's user defined field's section (this is based on which group(s) she or he belongs to OR

    B. Get rid of the "one table for all the users's demographic info" and instead just use the table for each group, and make copies for the individuals who belongs to more then one group.

    The first design sound better to me, I'm just wondering if one table with 80,000 rows (I'm thinking into the future) and 25 columns, worth of data, will run much slower then if it was divided into tables per group, like in design B? Also would it be perhaps a better idea to take design A, but break the main users table of 80,000 users into 26 tables, one for each letter of the alpabet sorresponding to their last name? That way it wouldn't have to scan and write to one huge table, but would use a table roughly 1/26 that size. I'm assuming each user will modify and write to the database once a month or so.
    I want to make sure I pick the best design, since I realize some things are much harder to change in the future.

    Any help would be great! Thanks to everyone whose taken the time to respond to questions on this BB, I thank you!
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    I'd go with option A. The reason is that, given the way you describe it, queries may be pulled across all groups for the standard demographic information which in option B would require joining several tables and then weeding out the duplicates.

Similar Threads

  1. Help with database design
    By jaypwillie in forum MySQL Help
    Replies: 0
    Last Post: November 20th, 2003, 10:05 AM
  2. Relational Database Design
    By phppick in forum PHP Development
    Replies: 1
    Last Post: November 19th, 2003, 06:37 AM
  3. newbie to postgreSQL, having problems with database design
    By sulat_mulat in forum Database Management
    Replies: 2
    Last Post: November 5th, 2003, 10:11 PM
  4. Database design based on XML schema
    By kavi_s in forum XML Programming
    Replies: 1
    Last Post: June 2nd, 2003, 04:26 PM
  5. Parts catalog database design problem (newbie)
    By peterg22 in forum Database Management
    Replies: 1
    Last Post: May 15th, 2003, 12:22 PM

IMN logo majestic logo threadwatch logo seochat tools logo