Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

What is the best way to allow for dynamic fields/database columns? For example, let's say we have a payroll system that allows a user to create unique salary structures for each employee. How could/should one handle this scenario? I thought of using a "salary" table that hold the salary component fields and joining these columns to a "salary_values" table that hold the actual values. Does this make sense?

Example Salary Structures:

Notice how the components of the salary can be shared or unique.

-- Jon's Salary --

Basic           100   
Annual Bonus    25   
Tel. Allowances 15

-- Jane's Salary --

Basic             100
Travel Allowances 10
Bi-annual Bonus   30
share|improve this question
add comment

6 Answers

up vote 4 down vote accepted

It will depend on the specifics of your case, but it might be best to take a metadata approach, where you have a salary_properties table which stores key-value pairs to let you flexibly give each employee a unique salary structure. This will be very flexible, but you can probably already tell that this table could get very large and unwieldly. Indexing can also be a problem. You may also want to read up on the Entity-Attribute-Value pattern, if you really think you need this kind of flexibility. EAV is not without issues though.


Based on your example, I don't think metadata or EAV is really necessary. I think a stand-alone salary table might be enough.

Salary
------
   employeeID
   salaryID
   base_amt
   bonus_pct
   bonus_type (annual or bi-annual)
   travel_expense_amt
   tel_expense_amt
   ...

I suppose if you had many types of expenses, you might want to break that out to a separate salary_expenses table, and if the types of expenses, then you might want to have a salaray_expense_type_id that refers to a salary_expense_types table, but otherwise, Keep It Simple!

share|improve this answer
    
This would have been my suggestion; EAV aka Key-Question-Answer allows for the persistence of just about anything that can be expressed as a varchar (or varbinary if you want to get really flexible). However, hydrating code objects from an EAV table is no walk in the park; you often cannot make use of an ORM library, and you have few or no rules concerning completeness of a record or referential integrity that can be enforced in the data layer. –  KeithS Jul 9 '12 at 18:42
    
@KeithS: Based on the OP's updates, I'm not sure that EAV (or similar) is really necessary. Maybe one or two columns to indicate the type of some other piece of data (such as when the bonus is paid) but if that's the extent of the flexibility, then I think a regular table will be fine. –  FrustratedWithFormsDesigner Jul 9 '12 at 18:48
    
Thanks for the insights. I've played around with a few of the suggestions, and I think I'm going to go with your (and @Rachel) suggestion of having a salaries table and a salary_types table, while separating allowances into a similar pair of tables. –  DanMark Jul 12 '12 at 15:12
add comment

The best way is to go vertically instead of horizontally. For example, a commission-based system that increases the commission based on number of units sold could be a table structured like this:

Units    Commission
10         .01
100        .02
300        .05
1000       .10
share|improve this answer
add comment

The "salary structures" you give as examples can probably be replicated using a structure similar to what I think you're describing, with a "salary" table that is the "point of access" for one or more components, which can be described in terms of type, amount and frequency:

Salary
   salaryId int PK

SalaryItem
   salaryItemId int PK
   salaryId int FK Ref(Salary.salaryId)
   description varchar(50)
   frequency int //maps to an enum in code, maybe also a FK to a lookup table
   amount money //or decimal(12,2)

You then have rules (which may or may not be persisted in the DB) that determine whether a particular SalaryItem should be applied to the person's paycheck depending on the "frequency" value to be applied; they should be applied on the first actual disbursement date that is equal to or greater than the function of the preceding disbursement date by which the frequency is defined. This is probably the trickier part; defining the function that determines the dates on which each frequency falls, and (if necessary) tracking the last day on which that frequency occurred.

share|improve this answer
    
That's exactly what I was trying to describe. It's only way I could think of having dynamic salary attributes/fields in a RDBMS. However, it doesn't seem all that clean since it allows for duplicate salary fields. In spite of this drawback, do you think this is the best approach? Any other approaches, either with a RDBMS or NoSQL db, worth looking at? –  DanMark Jul 9 '12 at 19:20
    
You can implement a salaryItemType field with a finite number of predefined values (another enum maybe), and define a unique constraint on the combination of salaryId and salaryItemType. Depending on how rigidly you enforce naming constraints on the "description" field in code, you could enforce uniqueness there (but I would recommend against it). That would allow you to enforce that no more than one "travel allowance" is assigned to each employee. –  KeithS Jul 9 '12 at 19:40
add comment

I would do this with two tables. The database is better normalized, and it allows for expansion easily if needed.

EmployeeSalaries table: (1 is Jon; 2 is Jane)

EmployeeId    SalaryTypeId    Amount    Frequency
1             1               100       1
1             2               25        1
1             4               15        1
2             1               100       1
2             3               10        1
2             2               30        .5

SalaryTypes table

Id   Description
1    Base Salary
2    Bonus                 
3    Travel Allowance
4    Telephone Allowance

If there are many different types of "Allowances", I would consider making a single "Allowance" salary type, and adding a Description or Note column to the EmployeeSalaries table

share|improve this answer
    
Not sure I'd really want to have "Travel Allowance" (or other allowances) be considered a "Salary Type". It's not a part of the employee's salary, it's an allowance for a very specific purpose, and in some cases is only paid after the proper expense claim paperwork has been approved. Which would lead to separate fields for tracking approvals (or rejections). –  FrustratedWithFormsDesigner Jul 9 '12 at 20:36
add comment

I am not sure which data base you are referring to, however if you use the PIVOT concept, you can easily achieve the nth level of your dynamic columns requirement.

share|improve this answer
add comment

In your example good approach would be separating Salary from Employee.

A Salary does not describe a Employee and should not be stored in the Employee table. Details of Salary should be stored in a Salary table, in which you could also record extra information about the salary , like when the salary was made, and what the salary commission rate, etc.

There are also some common database design mistakes to avoid that are nicely compiled in this post - Ten Common Database Design Mistakes

Edit: The detailed summary items need to be kept in a separate SalarayItem table. With a s one to many relationship between Salary->SalaryItem

share|improve this answer
1  
A good point, and that link is also a good one, but the OP doesn't actually say that they plan to store all of this salary information in an existing Employee table. –  FrustratedWithFormsDesigner Jul 9 '12 at 18:55
    
Thanks, If it the case then Salary->SalaryItems (one to many relationship) should do the trick. –  Yusubov Jul 9 '12 at 18:59
    
Interesting reference although I don't quite understand how using Stored Procedures help in database design. –  Otávio Décio Jul 9 '12 at 19:17
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.