Mysql Performance Question - Essentially about normalizing efficiency
- by freqmode
Hi there. Just a quick question about database performance. I'll outline my site purpose below as background.
I'm creating a dictionary site that saves the words users define to a database. What I'm wondering is whether or not to create a words table for each user or to keep one massive words table. This site will be used for entire schools so the single words table would be massive!
The database structure is as follows:
A user table with:
User_ID PRIMARY KEY  
Username   
First     
Last     
Password     
Email    
Country     
Research   
Standings   
SendInfo   
Donated     
JoinedOn      
LastLogin     
Logins      
Correct     
Attempts     
Admin     
Active
And one word table with:
User_ID PRIMARY KEY   
Word     
Vocab   
Spell     
Defined    
DefinedAttempted     
Spelled             
SpelledAttempted    
Sentenced     
SentencedAttempted
So what I'm asking is , performance-wise, should I create a new table for each user when they join the site - each user could have hundreds or thousands of words over time? Or is it better to have one massive table with thousands and thousands of records and filter by User_ID. I don't think I'll perform many table joins.
My gut feeling is to create a new table for each user, but I thought I'd ask for expert advice! Thanks in advance.