objects(such as person) and items (such salary items) data design structure

55 views Asked by At

This may be a classical requirement. Two key concepts objects and items, a time-change demand, the items can changed very quickly.

Such as a person have so many salary items, and these items will be added and changed or deleted.

Our solution is so simple, it's a 500 columns table, and map items to columns.

This beyond to hard code. This solutions have so many disadvantage.

**What's the classical solution about this requirement**
1

There are 1 answers

0
NoChance On

As I gather from your question, this is a classical one-to-many (1-M) relationship. The one side is the (object) and the many side is the (item). The item should have a column that points to its 'parent object'. This is column could contain the value of the Primary Key of the parent table (usually referred to as Foreign Key). The child table (the item) could have validity period (such as start date, end date) as well as other control columns in addition to the business columns (such as salary amount, etc.). This design is typical in Normalized database modeling.

There are many advantages of this design. One of them that pertains to you case, is that you don't have to have large number of columns, instead, you'd have several rows which allows you to fetch just what you want to satisfy most business queries. Similarly Create and Update operations will deal with much smaller rows, hence increasing the performance of the application.