sqlsql-serverdatabase-designbest-fit

What is best practice for design tables for variable information?

I am faced with the problem that you may have experienced it, There is a entity with formal field and variable field, For example consider a Person entity that have name and family and state, depend of state value

if state=1 then person must fill information about Education(many formal field and one to many information)

or

if state=2 then person must fill information about Healthy(many formal field and one to many information)

and etc.

During develop may be this condition changed and a state = 7(Insurance Information) also added that the user must filled if condition state = 7 is launched!

So what is best practice to resolve this problem and Design tables with comfort extension and least changes and easy query?

Note that Xml filed is hard to query(for report later by another person) and i temporarily ignored this solution.

Thanks.

Solution

  • Three choices:

    1. Think ahead of time and try to foresee all posible fields needed and compile them in your table.

    Lets say we got a client's table which can be both people and corporations.

    We'd do something like this in the table:

    example: 

    [Client]
first_name /* person */
last_name /* person */
commercial_name /* corporation */

    This will of course make you produce a quite big table if you handle a lot of data.

    1. You could handle those "variable values" as extra tables with a foreign key referencing the main table

    example: 

    [Person]
person_id
first_name /* person */
last_name /* person */

[Education_info]
person_id /* refrences Person.person_id */
.... /* info */
    1. Wordpress style, one main table, one "meta table".

    example: 

    [Person]
person_id
first_name /* person */
last_name /* person */

[Person_meta]
person_id /* references person */
key_name 
key_value

    then you'd have truly variable metadata belonging to the person.

    Hope it helps!