Record level and Field level validations 3

[vj]

hi everyone,

i'v never done or used the record level and field level validations in the table structure designer, i was wondering if i could get some expert advice on this issue .... i guess i never used it since i never had the time to try it out ,,, but now iam seriously think of implementing this feature ... can anyone tell me how often - good professional level of programming involves the practice of this feature. a basic idea and guideline for implementing these features would be a great help.

thankx alot
Vijay

 

[Dan Freeman]

Personally, I feel that validation is a function of the business. It is NOT a function of the database.

 

[vj]

that is true danfreeman .... but i was wondering on how to implement that useing the field level and record level option/feature in the table designer ... when defining the table structure... ! do u use define anything in those options when designing a table ?!

vijay

 

[vgulielmus]

I use very ofthe the default value.

In the following short demo, bb must be >0 and cc must be >3.
But also bb + cc must be >10

CLOSE DATABASES all
ERASE aa.*

CREATE DATABASE aa
CREATE TABLE aa (;
	aa C(10),;
	bb I CHECK bb>0 DEFAULT 1,;	&& Field validation Rule bb>0 Default 1
	cc I CHECK cc>3 DEFAULT 4,;	&& Field validation Rule cc>3 Default 4
	check bb+cc>10)			&& Record validation Rule bb + cc > 10
	
INSERT INTO aa VALUES ('Test1',0,11)	&& bb field validation failed
INSERT INTO aa VALUES ('Test2',111,2)	&& cc field validation failed
APPEND BLANK				&& record validation failed
INSERT INTO aa VALUES ('Test3',3,5)	&& record validation failed
INSERT INTO aa VALUES ('Test4',3,9)	&& ok
BROWSE

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Mike Lewis]

Vijay,

I assume you are referring to the valildation rules that are actually stored in the database (the DBC), that is, the rules that you specify in the table designer.

If so, the pros and cons are as follows:

FOR

- Keeps all the rules centralised. If a rule changes, you only need to change it in one place.

- Rules are automatically enforced for all applications. They don't need to be communicated to programmers or written in specifications.

AGAINST

- Makes the programming clumsy (in my opinion). Instead of checking for specific conditions at the point at which you accept or save the data, you have to handle validation through your error-handling (this is a bit of simplificationl; my main point is that the programming isn't as obvious as I would like).

- It's VFP-specific. If you ever move your data to a different back-end, you will have to re-code the rules for the specific database.

Personally, I never do this sort of validation. I find it more natural to simply check the values in either the Valid event or in a custom Validation method called from my Save routine. But that's just my opinion.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[vj]

hi mike,

so i guess ... just like having procedures in a external .prg files ... it is best not to have field level and record level validations in-built into the .dbc tables ... instead have it in the code ... !

thankx
Vijay

 

[Olaf Doschke]

Well,

Non NULLable fields are widely accepted, Referential integrity rules are widely accepted, but table rules not. I question that a bit.

The con arguments are taken and I share these oppinions, but if you have administrative users poking in the data directly via browse, such rules additional to referential integrity stored procs can prevent a lot of harm even without the application business logic running.

You can set the rules programmatically via
DBSETPROP("sometable.somefield","FIELD", "RuleExpression", "!EMPTY(somefield)")
DBSETPROP("sometable.somefield","FIELD", "RuleText", "somefield cannot be empty")

It's not hindering you and also does not does relieve you from writing more complex rule checks in code, which for example are not limited to look at the record level only and take into account application states, user permissions, data in related tables etc. So are RI rules.

Doing the same things with a field level rule is cumbersome via stored procs, so I'd keep it to the simple rules. Yes, it does not follow ANSI standards, as many things VFP, but can be done via constraints in other databases, for example, or on the trigger level.

To repeat rules is against the main OOP principle DRY (don't repeat yourself), but I would blame that on the non encapsulation of the data with the application. It introduces the need to formulate these rules twice. You can't and you won't want to, your data should be the persisted external output and value your application generates for your customers, but so is the data quality and validity.

The same thing is done with HTML form validation via Javascript, also in the age of ajax, for the benefit of less turn around times to a server for validation. And you still do double check and sanitize user input, before accepting and storing it.

Bye, Olaf.