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.
