Should I break a large user table into smaller tables for specific roles and information?
-2
votes
2
answers
72
views
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!
Asked by JayDev95
(97 rep)
Dec 9, 2024, 09:09 PM
Last activity: May 5, 2025, 07:55 AM
Last activity: May 5, 2025, 07:55 AM