SQl reference table - dynamic columns

I want开发者_如何学C to create a reference table. The number of rows and columns can grow (and become redundant) over time. The column names may change over time as well. Lets asume the value in Column 'Position' is 'IT Engineer', in row(n). For that specific row(n) further down in the column structure, lets say in column 'Behind Sheds', is a value I need to retrieve. If the number of columns was fixed, it was no issue, but now the columns are added dynamically with T-SQL, which is fairly easy to do, even renaming these columns. My question is, is it good practice to add columns dynamicaly for the example above, or is there an better alternative, and how?

Thanks.

may be better use something like addition table with fields: person_id (forign key to main table), person_param (as example 'Position'), person_value (as example 'IT Engineer')?

I believe your issue is the right for implementing the EAV model.

Basically instead of having 20 columns, you have 3:

• Entity
• Attribute (otherwise known as column name)
• Value (might actually be more values, of different types, and you just populate the right one)

That way, for 10 entities in the database, having 20 columns each, you populate 200 rows of data in an EAV table. It has less performance, it's harder to query, but it does allow flexibility, so you can make each entity have different sets of attributes even.