5

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.

Community
  • 1
  • 1
SleepingSpider
  • 1,168
  • 4
  • 19
  • 35

3 Answers3

6

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

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
3

"could splitting the table logically help, such that the employee information is captured in several INSERT statements?"

No.

How could it help?

20 fields is a fairly small number of fields for a relational database. There was a question a while ago on SO where a developer expected to have around 3,000 fields for a single table (which was actually beyond the capability of the RDBMS in question) - under such circumstances, it does make sense to split up the table.

It could also make sense to split up the table if a subset of columns were only ever going to be populated for a small proportion of rows (for example, if there were attributes that were specific to company directors).

However, from the information supplied so far, there is no apparent reason to split the table.

0

Briefly put, you want to normalize your data model. Normalization is the systematic restructuring of data into tables that formed the theoretical foundation of the relational data model developed by EF Codd forty years ago. There are levels of normalization - non-normalized and then first, second, third etc normal forms.

Normalization is now barely an afterthought in many database shops, ostensibly because it is erroneously believed to slow database performance.

I found a terrific summary on an IBM site covering normalization which may help. http://publib.boulder.ibm.com/infocenter/idshelp/v10/topic/com.ibm.ddi.doc/ddi56.htm

The original Codd book later revised by CJ Date is not very accessible, unfortunately. I can recommend "Database Systems: Design, Implementation & Management; Authors: Rob, P. & Coronel, C.M". I TA'ed a class on database design that used this textbook and I've kept using it for reference.

Andrew Wolfe
  • 2,020
  • 19
  • 25