When to split a Large Database Table?

Question

I'm working on an 'Employee' database and the fields are beginning to add up (20 say). The database would be populated from different UI say:

Personal Information UI: populates fields of the 'Employee' table such as birthday, surname, gender etc

Employment Details UI: populates fields of the 'Employee' table such as employee number, date employed, grade level etc

Having all the fields populated from a single UI (as you would imagine) is messy and results in one very long form that you'd need to scroll.

I'm thinking of splitting this table into several smaller tables, such that each smaller table captures a related information of an employee (i.e. splitting the table logically according to the UI).

The tables will then be joined by the employee id. I understand that splitting tables with one-to-one relationship is generally not a good idea (multiple-database-tables), but could splitting the table logically help, such that the employee information is captured in several INSERT statements?

Thanks.

Answer 1

Your data model should not abide to any rules imposed by the UI, just for convenience. One way to reduce the column-set for a given UI component is to use views (in most databases, you can also INSERT / UPDATE / DELETE using simple views). Another is to avoid SELECT *. You can always select subsets of your table's columns