I am designing a new database from scratch at the moment, trying to foresee any issues that may arise as scale increases
I'm making the login system at the moment
I have tables: (InnoDB, Dynamic)
- user (Updates on new user creation, only stores id, email, hash, pepper)
- user_profile (Updates on user modifying profile info - it is ecom, so irregular)
- user_addresses (Updates on user adding an address)
- user_reset (Updates on password recovery attempt)
- user_attempts (Updates on failed password attempt)
- user_devices (Updates on login)
At present, all tables have a foreign key of ID from "user" table - they may or may not have their own ID too depending if it is needed. "user_profile" also has foreign key of "email" from "user" table
I also plan to add marketing tables which foreign key from "user" table "id" in the future which will do anything from tracking traffic, to monitoring clicks, etc.
Will the setup I have perform optimally? Any suggestions of revisions I could make to improve performance? i.e. Do I even need foreign keys? Assistance would be greatly appreciated
.
Anticipating Your Questions:
Why split user and user_profile?
I'm hoping for performance gain on login... Maybe not? Not that familiar with foreign keys in all honesty, they have always seemed to hinder more than they help, but I really want to do things "right" from the beginning
Define "scale"
100k users on a website, since if it ever got to that size I'm pretty sure I could hire a team of developers lol
Edit: Thanks for -1 without any comment, glad you understand MySQL way better than I do
CodePudding user response：
6 updates -- not a big deal. Should be able to handle hundreds of users per second.
If any of those tables are "1:1", then consider combining them. (If they are "1:many", then separate tables is appropriate.)
Indexes are important for performance. A FOREIGN KEY is two things: an index, and a consistency check. Many textbooks push for using FKs; I am usually happy with just the equivalent index.