database modelling -mysql

I am doing the design of a database, that will have eventually thousands of users. Each user has your profile and specific data associated.

In your opinion, it is best practice a table for id, username, activationLink and hash and another for addres开发者_StackOverflow中文版s, age, photo, job, or it is best a unique table for all stuff?

thanks for your time

---

If:

1. All (or almost all) users have all data filled
2. Most of the time you query for all fields

then keep them in a single table, otherwies split them.

In your model, activationLink seems to be queried for only once per activation, so I'd move it into a separate table (which would allow deleting it after the account had been activated).

Address, age, photo and job are usually shown along with the username, so it would be better to merge them into a single table.

---

Don't allow your initial design to limit the ability (or just make it difficult) to expand your requirements in the future.

• At the moment, a user may have one address so you might put it in the users table - what if you want them to be able to store "work" and "home" addresses in future, or a history of past addresses?
• A user may only be allowed to have a single photo, but if you put it (or a URL for it) in users.photo, then you'd have to change your data structure to allow a user to have a history of profile photos

As Quassnoi mentions, there are performance implications for each of these decisions - more tables means more complexity, and more potential for slow queries. Don't create new tables for the sake of it, but consider your data model carefully as it quickly becomes very hard to change it.

Any values that are a strict 1-to-1 relationship with a user entity, and are unlikely to ever change and require a history for (date of birth is a good example) should go in the table with the core definition. Any potential 1-to-many relationships (even if they aren't right now) are good candidates for their own tables.