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!