Database design patterns – best practices?
I’m working on designing a database for a project and I want to make sure I’m following best practices from the start. I’ve learned about normalization but I’m not sure if I should normalize everything completely or if there are cases where denormalization makes sense. Also, what’s the best approach for handling relationships – should I always use foreign keys? And is there a particular design pattern that works well for user authentication data? I want to avoid common pitfalls and build something scalable. Any advice would be appreciated!
5 Replies
Join the discussion.
Log In to Reply
real talk: normalize first, optimize later. most beginners over-optimize and create maintenance nightmares. foreign keys are ur friend, theyre not expensive. for passwords use bcrypt or argon2 thru a library, never try to hash urself. thats how u end up on news.ycombinator lol
Ha okay good to know! So basically don't try to be clever with passwords and stick with proven libraries. That makes sense. I'll normalize completely and use foreign keys, then see if I have performance issues later.
Solid advice above. I would add: design with auditing in mind from the start. Include created_at and updated_at timestamps. Consider soft deletes for sensitive data. These decisions are difficult to retrofit later.
For authentication specifically, look into OAuth2 or similar patterns rather than rolling your own. Lots of services handle this now and it's more secure. If you do custom, separation of concerns is key - auth logic shouldn't be mixed in with business logic.
normalize to 3nf usually, then denormalize only when u have specific performance issues. use foreign keys always - they prevent bad data. for auth, never store passwords in plain text (should be hashed), and keep sensitive data separate from main user table