Relational Database Design for N dimensional function, I have some problems
-
My task is: Our users get from their chemical suppliers some tables do determine the required quantity for chemical Y based on the given quantity of chemical X. BTW: The task is_not_ to find a linear regression formula. Easy example- 2 Dim: Depending on X the component Y has to be used with different quantities:
X Y= f(X)
1 0.1
2 0.2
3 0.5In Praxis this will be not only y= f(x) it is more r=f(x,y,z, ...) and much more it is (r, s, t, ...)= f(x, y, z, ....) but the later is not the discussion here. Now I'm asking me how one can design a General database layout with a constant number of tables/fields to save an N dimensional function. With constant number of tables/fields I mean I don't like to solve this task by creating "dynamicly" columns according to the bigest dimension in use... or so ;) Note: I have also to say most probably I’m not going to save this kind of data in a set of related tables, but anywhere I’m interesting how one would do it theoretically, maybe this gives me some more ideas and last but not least to learn. The dimensions are usually around 3 to 5, but I like to solve: How can one design a General Database Table Layout -for N dimensions - for this task? Is started like this
// TBLS: The Main Table to define a specific N Dimensional User- Function)
TBLS
ID (P)
NAME// TBLS_BASES: In this table the bases of the User Function will be defined
TBLS_BASES
ID
TBLS_ID (P) FK: TBLS.ID
BASE_ID (P)
BASE_NAME// TBLS_BASES_VALUES: In this table the bases values will be defined
TBLS_BASES_VALUES
ID
TBLS_ID (P) FK: TBLS_BASES.TBLS_ID
TBLS_BASES_ID (P) FK: TBLS_BASES.BASE_ID
POS (P)
VALUE // The base value, basically the candidate for PrimKeySeg, but
// replaced by POS for the sake of simplifying// TBL_VALUES: The table to finally save the function values. Here I have my big problem
TBL_VALUES
TBLS_ID FK: TBLS_BASES_VALUES.TBLS_ID
TBLS_BASES_ID FK: TBLS_BASES_VALUES.TBLS_BASES_ID
POS FK: TBLS_BASES_VALUES.POS
VALUE // Finally the function valueNow an example for three dimension z= f(x, y) First the three "base" tables for the function
TBLS TBLS_BASES TBLS_BASES_VALUES
====== ========================== ==========================================
ID BASE_ID TBLS_ -
My task is: Our users get from their chemical suppliers some tables do determine the required quantity for chemical Y based on the given quantity of chemical X. BTW: The task is_not_ to find a linear regression formula. Easy example- 2 Dim: Depending on X the component Y has to be used with different quantities:
X Y= f(X)
1 0.1
2 0.2
3 0.5In Praxis this will be not only y= f(x) it is more r=f(x,y,z, ...) and much more it is (r, s, t, ...)= f(x, y, z, ....) but the later is not the discussion here. Now I'm asking me how one can design a General database layout with a constant number of tables/fields to save an N dimensional function. With constant number of tables/fields I mean I don't like to solve this task by creating "dynamicly" columns according to the bigest dimension in use... or so ;) Note: I have also to say most probably I’m not going to save this kind of data in a set of related tables, but anywhere I’m interesting how one would do it theoretically, maybe this gives me some more ideas and last but not least to learn. The dimensions are usually around 3 to 5, but I like to solve: How can one design a General Database Table Layout -for N dimensions - for this task? Is started like this
// TBLS: The Main Table to define a specific N Dimensional User- Function)
TBLS
ID (P)
NAME// TBLS_BASES: In this table the bases of the User Function will be defined
TBLS_BASES
ID
TBLS_ID (P) FK: TBLS.ID
BASE_ID (P)
BASE_NAME// TBLS_BASES_VALUES: In this table the bases values will be defined
TBLS_BASES_VALUES
ID
TBLS_ID (P) FK: TBLS_BASES.TBLS_ID
TBLS_BASES_ID (P) FK: TBLS_BASES.BASE_ID
POS (P)
VALUE // The base value, basically the candidate for PrimKeySeg, but
// replaced by POS for the sake of simplifying// TBL_VALUES: The table to finally save the function values. Here I have my big problem
TBL_VALUES
TBLS_ID FK: TBLS_BASES_VALUES.TBLS_ID
TBLS_BASES_ID FK: TBLS_BASES_VALUES.TBLS_BASES_ID
POS FK: TBLS_BASES_VALUES.POS
VALUE // Finally the function valueNow an example for three dimension z= f(x, y) First the three "base" tables for the function
TBLS TBLS_BASES TBLS_BASES_VALUES
====== ========================== ==========================================
ID BASE_ID TBLS_In your TBLS have a parent ID which is self referencing. I would also store a text structure of your formula in that table. This is a straight forward bill of materials structural requirement. Have you considered a mathematical formula parser it may allow you to store the text and decompose it to elements on the fly (caveat I have no idea of their capabilities or accuracy).
Never underestimate the power of human stupidity RAH
-
In your TBLS have a parent ID which is self referencing. I would also store a text structure of your formula in that table. This is a straight forward bill of materials structural requirement. Have you considered a mathematical formula parser it may allow you to store the text and decompose it to elements on the fly (caveat I have no idea of their capabilities or accuracy).
Never underestimate the power of human stupidity RAH
Thank you for your Response. Yes finally I will save it as text and parse it. It is only curiosity how a structure should look for a relatinal DB. After reading agein
Quote:
In your TBLS have a parent ID which is self referencing
Great idea, thank you so much!:thumbsup: