MySQL Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesMySQL Help

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old May 17th, 2000, 09:26 AM
mystic mystic is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
 
Join Date: May 2000
Posts: 3 mystic User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation 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!

Reply With Quote
  #2  
Old May 17th, 2000, 12:09 PM
rod k rod k is offline
Apprentice Deity
Dev Shed Loyal (3000 - 3499 posts)
 
Join Date: Jul 1999
Location: Niagara Falls (On the wrong side of the gorge)
Posts: 3,237 rod k User rank is Private First Class (20 - 50 Reputation Level)rod k User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 4 m 8 sec
Reputation Power: 13
Send a message via AIM to rod k
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.

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesMySQL Help > database design questions


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway
Stay green...Green IT