0

I have 3 tables: Persons, Variables, Person_Data.

Person_Data table has numerical data on various variables for different persons. Columns are: variable_value, person_id (foreign key to Persons) and variable_id (fk to Variables).

Some of the variables are related to each other (for example: Income, Family size and Per-capita-income). I want to create a Variable_Relationship table to store this type of information and perform data sanity check. One of the column in the table would be Dependant_Variable_Id (LHS of the relationship).

  1. The issue is that the number of RHS variables is not fixed and neither is the mathematical expression.
  2. Is there a way to implement this nicely?

Right now I am thinking about a relationship_definition text column along with another table that has Relationship_Id and RHS_VariableId columns.

ymn
  • 2,175
  • 2
  • 21
  • 39
user1936014
  • 1
  • This difficulty is typical of EAV data models unfortunately. See this wiki article: http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model and consider whether you really need this model. You will largely have to implement relationships and constraints in your application rather than having them enforced by the database. – Lord Peter Dec 29 '12 at 08:09

1 Answers1

0

In my opinion there is no way to manage it in SQL, since you have no way to interpret dynamically formulas expressed in column values. Depending on the language you use to access data on the database, you should develop an expression parser (you can search for plenty of open source libraries providing such a feature) and use it to parse the expressions read from the RHS column, evaluate them and perform sanity checks.

remigio
  • 4,101
  • 1
  • 26
  • 28