Click here to Skip to main content
15,168,423 members
Please Sign up or sign in to vote.
3.67/5 (2 votes)
See more:
could be a hard one to describe

I need to store requirement values in a database, no problem, right?
Here's the skinny. At run-time, a user will pass into some procedure a set of acquired values.

example: C = 0.34, Cr = 1.00, Fe = 96.6 : and says, "see if this meets the requirements of x entry in data base".


here is where my problem is.

The "X" entry in database needs to somehow be phrased as fallows.

(C must be at least 0.2 and at most 0.5)
(Cr must be under 1.20)
(Fe > 92.00)
and here is the super, tricky one
(Other [Cr + Fe + C] > 97.95)

Other, will not always be the same and for most database entries it's not even used.
But it does have the potential the be used frequently and will rarely ever be the same.

Anyway, the acquired values need be compared to the specification provided by the database entry
to determine a Pass/Fail.

I'm anticipating thousands of entries into the database over a long period of time.


If anyone can help me find a solution for this I would appreciate that very much. If my desired result is unclear then let me know and I will try to provide more details.


------------- Update ------------------------------
Please see ArunRajendra's question and the response.

Here is a link to an excel file (Opens in browser) to show the database structure ... ish.
Excel File That Opens In Browser

Here is a link to an image for an At a glance view of the DB
https://skydrive.live.com/redir?resid=1A24B4A8C01B42BB!1180&authkey=!ALALWyEOSlFfCuM&v=3[^]
Posted
Updated 2-Sep-13 11:34am
v4
Comments
ArunRajendra 30-Aug-13 0:08am
   
Need the logic how the values 0.2, 0.5, 1.20, 92.00 and 97.95 are obtained.
Mr.TMG 31-Aug-13 0:45am
   
Those example values would theoretically be entered into a database (I'm posting a link) by means of a vb.net interface. The values are determined by the user and stored (as a specification) so that other users can determine whether or not they have acquired values that are consistent with it.
The other users will simply input the values that they have acquired, select the appropriate specification to compare them to, and sit back as the Acquired values are compared to the specification values to determine A: the acquired values match, or B:The acquired values did not meet the specification.
phil.o 30-Aug-13 11:24am
   
Do you have an existing DB design to start from ?
Mr.TMG 31-Aug-13 18:54pm
   
Yes, Please see the updated question.
Mr.TMG 2-Sep-13 10:54am
   
I'm in no hurry with this problem btw. It's part of a project that I'm planning for sometime next year.

This type of validation can be handled using the DataTable class with via validator column with the appropriate column expression. The expression must follow the the rules stated here: http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx[^]

I put the following example together before looking at your slides where it looks like there is a single validating criteria versus the multiple criteria that I assumed based on your post, but it should give you the basic idea.

Dim dt As New DataTable
Dim row As DataRow
' define the validation as a boolean equation stored a String
Dim C_validator As String = "[C] >= 0.2 And [C] <=0.5" '(C must be at least 0.2 and at most 0.5)
Dim Fe_validator As String = "[Fe] > 92.00" ' (Fe > 92.00)
Dim Cr_validator As String = "[Cr] < 1.20" ' (Cr must be under 1.20)
Dim Other_validator As String = "([C] + [Fe] + [Cr]) > 97.95" '(Other [Cr + Fe + C] > 97.95)
With dt
   .Columns.Add("C", GetType(Double))
   .Columns.Add("Fe", GetType(Double))
   .Columns.Add("Cr", GetType(Double))
   .Columns.Add("C_validator", GetType(Boolean), C_validator)
   .Columns.Add("Fe_validator", GetType(Boolean), Fe_validator)
   .Columns.Add("Cr_validator", GetType(Boolean), Cr_validator)
   .Columns.Add("Other_validator", GetType(Boolean), Other_validator)
   .Columns.Add("AllPassed", GetType(Boolean), "[C_validator] And [Fe_validator] And [Cr_validator] And [Other_validator]")
   row = .NewRow
   .Rows.Add(row)
End With

' set the data values
row("C") = 0.1
row("Fe") = 98.1
row("Cr") = 0.5
' check a validation column
MsgBox(row("AllPassed").ToString)
   
Comments
Mr.TMG 4-Sep-13 14:49pm
   
You were correct. there are multiple criteria to match. The [Spec Value] table was designed to hold a list of values to match. Each row points to the appropriate Spec in [Specs].

In the example you posted, it appears that you are adding values to a data table to view. This is something that I already have a design for.

What I'm trying to do is find a way to store a formula in my data base so that it can be used at run time. My main concern is storing the formulas for [Other Criteria]. A user Must be able to add new formulas to the database and my .net application must be able to read them and use the formulas to determine a value. That value will be checked to see if it falls within a pre-specified Min-Max range that is contained somewhere in the formula. The user provides values that they received for C, Fe, W, Cr etc... the program picks the values for the elements mentioned in the formula. The result is then calculated using the formula.
TnTinMn 4-Sep-13 21:04pm
   
"In the example you posted, it appears that you are adding values to a data table to view."

Not exactly, what I was trying to demonstrate was how to use a column with a boolean expression that would be the criteria that you store in the database. Look at the the string constants that I defined "xx_validator". You would store these string value in your database and then apply them as the column expression in the datatable. The datatable class will evaluate them based on the data.

"A user Must be able to add new formulas to the database and my .net application must be able to read them and use the formulas to determine a value. That value will be checked to see if it falls within a pre-specified Min-Max range that is contained somewhere in the formula."

I would suggest that your create an expression editor that controls how this information is entered. Then your program can translate it into a valid expression to evaluate.
Take a look at: http://www.codeproject.com/Articles/90589/Visual-Expression-Builder-for-Dynamic-Linq
for some ideas on how you could lay this out.
This is purely based on parsing the input. First of all, you need to parse each and every word of the basic input. that is, C = 0.34, Cr = 1.00, Fe = 96.6; When you are done, you need to know what a user has asked in his question. In your case "see if this meets the requirements of x entry in data base" but a user may ask the same question in a different way. So, this is the kind of natural language processing which can be very hectic. So, for that part, I will suggest you to provide user the pre-defined questions as a list of options and let user select one question from those. In that case your application will have an idea about which question was selected by the user and you can easily get the answer. Otherwise you can parse the questions asked by the user.
   
Comments
phil.o 30-Aug-13 11:23am
   
I'm not sure the problem lies in parsing the inputs. I do understand it rather as storing the prerequisites in the database. Mr.TMG can you confirm/infirm that ?

Unfortunately I will not have time for that today (I'm about to leave my computer for the week-end). But it's a rather interesting subject, and I will try to give it a think on monday morning, if it is still active and nobody has answered yet.
Mr.TMG 31-Aug-13 0:31am
   
Confirmed. I am attaching links to an image and excel doc onto my question.
Mr.TMG 31-Aug-13 1:07am
   
Sorry, SaqibRasheed, for the misunderstanding. I did not mean that the user would literally ask any questions. Rather, I mean that a user supplies values and a Specification(from the database) to compare the values to. Please see the Update at the bottom of my question.

I am actually curious about this natural language processing now. But, knowing myself as I do, I dare not venture into those waters until completing the rest of my projects.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900