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

Question

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**

"it's a 500 columns table" - what could possibly be wrong with a 500 column table.... — Mitch Wheat, Dec 04 '13 at 01:14
It's a sparseness table,and so many columns is no value for some objects. — Thai Heng, Dec 04 '13 at 01:55
What happens when an object has 501 items? Suggest you follow @Emmand's advice below - and do some reading on the principles of Normalisation. — sfinnie, Dec 04 '13 at 09:43

NoChance · Answer 1 · 2013-12-04T01:32:05.667

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.

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

1 Answers1