Table level validation

[leovinous]

I am doing some work on a table level validation and this one is just mystifying me. :banghead:
so here is my problem...
plane maximum payload (cargo weight) is 2500 pounds, if there are passengers then the maximum payload (cargo weight) is 1500 pounds.

I just keep banging my head on it...it is really annoying.

 

[boblarson]

Why are you doing this at table level? Nobody should be working directly in the tables anyway so you should be able to use a form and then use the form's BEFORE UPDATE event to validate.

 

[ButtonMoon]

I am doing some work on a table level validation and this one is just mystifying me. :banghead:
so here is my problem...
plane maximum payload (cargo weight) is 2500 pounds, if there are passengers then the maximum payload (cargo weight) is 1500 pounds.

I just keep banging my head on it...it is really annoying.

Maybe this example will help you. Depending on your version of Access Jet/Ace you may have to execute this code against a local or remote connection rather than in the SQL window.

ALTER TABLE flight ADD CONSTRAINT ck1 CHECK (cargoweight <= 2500 AND passengers = 0 OR cargoweight <= 1500);

Why are you doing this at table level? Nobody should be working directly in the tables anyway so you should be able to use a form and then use the form's BEFORE UPDATE event to validate.

Putting data integrity rules into the database usually has many advantages over application-level validation. Putting data integrity in the database means you have just one place to maintain the constraints so you don't have to update and keep every form and application in sync. An event-driven validation in procedural code is not equivalent to a database CHECK constraint. The event-driven code only checks the data from the point at which the code was created and in effect. Looking at the data a year or two later you may not even know whether and when the event-driven validations were effective or whether they applied to all data in a table. The CHECK constraint guarantees that all the data in the tables complies, no matter when it was entered. CHECK constraints are also available to the query optimizer to enable query rewrites; application-level validation code is not and cannot be used that way. My advice would be to put data integrity into the database as constraints whenever practical.

 

[Galaxiom]

I am doing some work on a table level validation and this one is just mystifying me. :banghead:
so here is my problem...
plane maximum payload (cargo weight) is 2500 pounds, if there are passengers then the maximum payload (cargo weight) is 1500 pounds.

So all planes in your system have the same constraint?

Or do you have a different table for each plane?

 

[Galaxiom]

Putting data integrity rules into the database usually has many advantages over application-level validation. Putting data integrity in the database means you have just one place to maintain the constraints so you don't have to update and keep every form and application in sync.

Surely one would not hard code the constraint into a form? That is what your post implies so it is not a fair comparison.

The form level constraints should be read from another table so there is still just one place to maintain them. Moreover it would allow for different planes in the same table.

An event-driven validation in procedural code is not equivalent to a database CHECK constraint. The event-driven code only checks the data from the point at which the code was created and in effect.

If such code is introduced to the application then the preexisting data would be checked with a simple query.

Moreover since a contraint did not previously exist then it may not have previously been applicable. Any data which did not comply with the new table level constraint would not be able to be stored. How doyou manage that?

A table level constraint leaves the user with a stock message. A form level validation can be managed much more elegantly. If you catch the error from the table so you can control the user expereince you still have to have code so why not validate it in that code before it is submittted to the engine?

 

[stopher]

The principle of putting all validation at table level is certainly the one I was taught. The principle behind this is as follows:

The table level design should handle the all aspects of the the table design and integrity such as relationship constraints and other constraints. In other words the table schema should stand on its own as a schema that represents logical model of the physical world. It should not be possible to create data in the tables such that the integrity of the data is in question.

The user level design should handle how the user interfaces with the database e.g. screen design, layout, feedback. This level is entirely independent of the table level.

If you don't subscribe to this principle then you might as well forego any constraints in the table design. That includes relationships and indexes! But then you rely wholly on the interface designer to pick this up consistently.

The reason for such a framework is more evident in large enterprise solutions where a table level schema may be the "back end" to multiple interfaces e.g. Access front end, Web, mobile devices, other 3rd party apps. These interfaces may have been developed by independent developers and even developers independent of those who designed the table schema. Of course any developer could be expected to follow some documentation and therefore should implement the given constraints. But the owner of the data simply can't risk this. Instead, they make their data integrity as secure as possible.

The solution that Buttonmoon gave is exactly how I would implement the constraint if this were a server based solution. The only caveat here is that we are in Access. The solution of course works fine. However, once implemented it is very difficult to see. You can change it - but you have to know it is there. Of course you will soon know it is there when you try to break the constraint (albeit a non-user friendly message). Compare this with a server CHECK constraint which is easily visible and easily changed.

Personally I still always go for table level constraints for the reasons above. I have a bad memory and my documentation isn't great so knowing the constraint is embedded works for me.

As regards the message, the since messages are the responsibility of the user interface then I would implement appropriate code to check and provide an appropriate message when necessary. You have the same problem for foreign keys etc. It would be nice if CHECK threw an error of custom message but it doesn't afaik.

Chris

 

[Pat Hartman]

@leovinous
You haven't given us any idea what version of Access you are using and if your BE is actually ACE or even Jet.

ACE has data macros which work sort of like triggers in SQL Server and other RDBMS'. Jet has no such feature and I think the feature was only added in A2010 so if you are using an earlier version of ACE, you may not have that ability.

The suggestions given so far assume that both the weight and passenger count are in the same record. In a properly normalized database, they wouldn't be. So, one or both of them actually requires a query to count the passengers and/or sum the weight. I don't know if data macros can do this since most of my BE's are SQL Server but I do know that triggers can.

 

[Galaxiom]

Both Buttonmoon and Stopher have overlooked the simple reality that I pointed out in my first post.

This contraint would ONLY work if every plane in the system had the same cargo limits. In such a case it would be permanently limited to that condition. No other planes could be added tot the system.

As such it is an unrealistic example. ButtonMoon's advice is completely inappropriate because confusing the schema with the data.

 

[ChrisO]

Is not the original question a bit strange; who would accept an aircraft takeoff weight based on an Access database?

Surely such a thing would be governed by some regulatory body or, at the very least, the aircraft manufacture.

It’s a whole lot more complicated than just passengers verses cargo. From what I have just read, even the same model of aircraft can have different certification depending on specific construction. It also depends on local conditions such as barometric pressure, altitude, ambient temperature, length of runway and rolling resistance of runway.

It is extremely unlikely that an Access database would have that live input data just prior to takeoff. And even if it did I would think that the database, whatever it is, would have to be certified.

Perhaps a good question would be to ask the OP how the database is going to be used.
It might just be a homework question…

Chris.

 

[Galaxiom]

Is not the original question a bit strange; who would accept an aircraft takeoff weight based on an Access database?

Perhaps someone who would accept a flight control system built on Windows?

Seriously, nobody would use Windows on a mission critical application would they?

Years ago worked on a project designing a five kilowatt wind turbine. The turbine blades were designed by a university and they also constructed their own machine.

The turbine had a very high tip speed ratio and used stall governing. There was no pitch control so it was important to maintain a load at all times or the thing would fly to pieces even in a light breeze.

There are of course many different ways the load could be lost. Failure of the generator, a break in the transmission wires etc.

I designed a simple automatic centrifugally tripped emergency brake. The university put a 15KW generator on theirs with the idea that it could not possibly burn out.

Then they controlled the generator fields using a Windows computer.

I guess don't need to tell you what happened to their machine.

 

[ChrisO]

I don’t wish to get off topic here but primary safety these days is most likely entrusted to a spring. There was a time when gravity was the primary safety but that had consequences which we do not need to go into here.

Another method is to triplicate the safety circuit so that if any one of the circuits fails then the system assumes a safe condition. That is not necessarily true of high voltage transmission systems but, that again, is not worth going into here.

The point I’m trying to make is that the database, as suggested, would be almost impossible to construct for the apparent usage. And even if it could, the cost of certification would be enormous.

So I do not see any reason to discuss any form of validation if the database can not be used.

Chris.

 

[Galaxiom]

So I do not see any reason to discuss any form of validation if the database can not be used.

Indeed. But aside from that there is the general issue of table validation for such a purpose as outlined.

I still say that those who are promoting table level validation in this kind of instance are barking up the wrong tree.

 

[ButtonMoon]

Both Buttonmoon and Stopher have overlooked the simple reality that I pointed out in my first post.

This contraint would ONLY work if every plane in the system had the same cargo limits. In such a case it would be permanently limited to that condition. No other planes could be added tot the system.

As such it is an unrealistic example. ButtonMoon's advice is completely inappropriate because confusing the schema with the data.

Hi Galaxiom,

My reply was just intended as a basic example of syntax to get leovinous started. It's perfectly possible to modify it to support the more realistic scenario where the weight limit is in another table. I'm not sure why you think otherwise but maybe I'm missing your point.

ALTER TABLE flight ADD CONSTRAINT ckweight CHECK
((SELECT maxweight FROM aircraft WHERE aircraft.regno = flight.regno)
>= flight.cargoweight ));

Database level constraints are a pretty powerful tool and widely used for this sort of thing. I can recommend Joe Celko's books SQL for Smarties and SQL Puzzles and Answers, which has a lot of very good examples of how to use database constraints.

 

[Galaxiom]

My reply was just intended as a basic example of syntax to get leovinous started. It's perfectly possible to modify it to support the more realistic scenario where the weight limit is in another table.

OK. I'll pay that one.

 

[stopher]

I can recommend Joe Celko's books SQL for Smarties ...

Me too. Fascinating book that really went way beyond my current knowledge. Although the point about table level constraints is pretty standard teaching I think for those learning standard SQL.

Interestingly Access doesn't really go out of its way to expose the more creative SQL. Instead ppl are led to believe that you can only do what the Access interfaces cater for. It was some time before I even realised I could do a sub query.

 

[stopher]

It might just be a homework question…

Chris.

Good point. Would a developer for a mission critical app really be doing this in Access and raising this kind of question? Could also be a hobbyist thing e.g. plane spotting?