When do I need to split a large database table?

Question

I am working on an Employee database and the fields are starting to add up (20 say). The database will be populated from different user interfaces:

Personal Information User Interface: Fills in the fields of the Employee table, such as birthday, last name, gender, etc.

Employment Details UI: Fills in the fields of the Employee table, such as employee number, date used, grade level, etc.

Having all the fields filled with one user interface (as you think) is useless and leads to a very long form that you need to scroll through.

I am going to split this table into several smaller tables, so that each smaller table captures the employee related information (i.e. logically splits the table according to the user interface).

Then the tables will be combined by the employee identifier. I understand that splitting tables with a one-to-one relationship is usually not a good idea ( multiple-database-tables ), but can splitting a table logically help so that employee information is captured in multiple INSERT operations?

Thanks.

Answer 1

Your data model should not comply with any rules entered by the user interface, just for convenience. One way to reduce the column set for a given user interface component is to use views (in most databases, you can also use simple views in INSERT / UPDATE / DELETE ). Another is to avoid SELECT * . You can always select subsets of table columns.

Answer 2

"can logically help split the table, so that employee information will be recorded in several INSERT statements?"

No.

How can this help?

20 fields is a fairly small number of fields for a relational database. Some time ago there was a question about SO, where the developer expected to have about 3,000 fields for one table (which actually did not match the capabilities of the RDBMS under consideration) - under such circumstances, it makes sense to split the table.

It may also make sense to split the table if a subset of the columns will only ever be filled for a small part of the rows (for example, if there were attributes specific to company directors).

However, from the information provided so far, there is no obvious reason for splitting the table.

Answer 3

In short, you want to normalize your data model. Normalization is the systematic restructuring of data into tables that form the theoretical basis of the relational data model developed by EF Codd forty years ago. There are levels of normalization - not normalized, and then the first, second, third, etc. Normal forms.

In most database stores, normalization is now almost an afterthought, ostensibly because it is mistakenly believed that it slows down database performance.

I found a terrific resume on the IBM website where normalization might be considered. http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.ddi.doc/ddi56.htm

The original Codd book, subsequently revised by CJ Date, is unfortunately not available. I can recommend “Database Systems: Design, Implementation, and Management,” Authors: Rob, P., and Coronel, CM. ”I am the TA'ed database design class that used this tutorial, and I continued to use it for reference.