Should I break a large user table into smaller tables for specific roles and information?

Question

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 question is:

Is splitting the users table into smaller tables based on their context (work, education, etc.) a good practice?

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

Thank you for your advice!

Answer 1

Breaking a large user table into smaller, context-specific tables is generally considered a good practice in database design. This approach follows the principles of database normalization, which helps organize data to reduce redundancy, improve scalability, and enhance query performance.

You could normalize your tables as follows:

Entity: User

Attributes:
    id: int
    name: str
    email: str
    phone_number: str
    address: str

Entity: WorkExperience

Attributes:
    id: int
    user_id: int (foreign key to User)
    job_title: str
    company_name: str
    years_experience: int

Entity: Education

Attributes:
    id: int
    user_id: int (foreign key to User)
    degree: str
    institution: str
    graduation_year: int

---

the gist of it is for entities work_experience and education to reference their related user. This makes maintaining the tables easy as you can update one without affecting the other.

Answer 2

You should break the table up based one what is 1-to-many relationships. For example a user might have more than one degree, so education should be a separate table from user, with a foreign key to user. This allows a user to have 0 or multiple degrees.

You should not break tables up purely on context, if the tables end up having a strict 1:1 relationship. This will just make insertion and deletion unnecessary complex and error prone.

Answer 3

As opposed to another answer: no, you should not.

Normalization is a principle invented in a time where storage was expensive, and is used for space-optimization as opposed to time optimization.

If you need speed (and you say you do), denormalize. Put all the data in a single table, read all columns in a single table query.

Basically:

Querying a single table is fastest select * from users where ...

Querying multiple tables in a join is slightly slower

select
  *
from
  users u
  join work_info wi on  ...
  join education e on ...
where 
  ...
  

You don't have any advantage doing this it this way, as you still query exactly the same columns, you just put more load on the database.

Doing multiple separate queries is the slowest you can do:

select * from users where ...

if (user.isStudent)
    select ... from education ...
     
if (user.isWhatever)
    select ... from work_info ...

As you will have multiple separate interchanges between application code and database. Networks are slow compared to processors.

So, for speed, keep it in one table.