0

I am designing a database for a system that has a users table. Currently, the table has around 50 columns, which include:

  • Personal information (e.g., name, email, phone_number, address, etc.)
  • Work-related information (e.g., job_title, company_name, years_experience, etc.)
  • Education-related information (e.g., degree, institution, graduation_year, etc.)

Some of these columns are only applicable to specific roles, like employees or students, while others are common to all users. I’m considering breaking the users table into smaller tables, such as:

  • A main users table for general information.
  • A work_experiences table for job-related details.
  • An education table for academic details.

My questions are:

  1. Is splitting the users table into smaller tables based on their context (work, education, etc.) a good practice?
  2. Will this approach impact performance negatively, or is it better for maintainability?
  3. Should I consider alternatives like using JSON columns for role-specific data or polymorphic relationships for related models?

I’m using MySQL, and performance is a concern since the system will scale to thousands of users.

Thank you for your advice!

Improve this question
New contributor
JayDev95 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
5
  • 1
    You haven't shown any queries you will use to query these data. Performance strategies must be chosen in the context of the specific queries you need to optimize. Every optimization strategy optimizes one type of query at the expense of other types of queries, so you need to know which queries are the priority.
    – Bill Karwin
    Commented Dec 9 at 23:04
  • Splitting into smaller tables for subtypes is not DB normalization. Why do you think it is? If you ask about it explain why you think it is & how you are 1st stuck.
    – philipxy
    Commented Dec 10 at 1:04
  • Ask 1 specific researched non-duplicate question per post. PS Including, not asking multiple questions via AND & OR. Yes or no questions are seldom useful or asking what the asker actually wants answered. Given this post clearly your questions are very likely to be duplicates already asked & answered many times. PS Please avoid social & meta commentary in posts.
    – philipxy
    Commented Dec 10 at 1:09
  • 1
    Separating work and education into separate tables seems fundamental to me, since each user can have multiple occupations (historically or even simultaneously). Same for education. This is normalization 101. It is definitely best for maintainability.
    – Albert Godfrind
    Commented Dec 10 at 11:07
  • 1
    As for performance, you talk about thousands of users which should not be any issue.
    – Albert Godfrind
    Commented Dec 10 at 11:11

1 Answer 1

Reset to default
1

  • For "thousands", splitting (or not) will make little difference to performance. Even for millions, it may not matter.

  • For maintainability and performance, let's see the main queries.

  • JSON may be an easy way to store miscellany. And it is flexible and expandable. But it is not performant for searching.

  • Sketching out both schemas (and queries) is a good learning exercise. Do it.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.