Re: A philosophical newbie issue: catch redundant errors via relationships or programmically?

"raylopez99" <raylopez99_at_yahoo.com> wrote in message news:788634eb-c652-4a03-bddd-17e4e409aeb8_at_j20g2000hsi.googlegroups.com... On Dec 30, 10:40 am, mAsterdam <mAster..._at_vrijdag.org> wrote:

> BTW, I notice a tension between not just front end GUI and back end dB
> schema people, but between putting data traps and data crunching code
> in the GUI front end versus writing a SQL query or otherwise dealing
> with the data in the back end. In fact, some people (the posters at
> microsoft . public . access . formscoding and elsewhere) imply and
> state that it's better to deal with this stuff at the front end, since
> SQL "puts too much load onto the servers" (or in particular Access), a
> preformance issue, while others, I suspect you guys, think that the
> front end technique is too much of a maintenance problem or is bad
> design.

Speaking just for myself, I have a bias in favor of coding the constraints into the DB schema
whenever possible, and paying the price, if there is one, in performance. I'm going to explain where that bias comes from, but I'll warn you up front that it's not relevant to your situation where you are the only database designer, the only database development programmer, and the only user (I presume) as well.

When I was working with databases in practice (not in theory) I was working in situations where there were twenty programmers for every database designer. (It varied from time to time, but you get the idea). The consequences of delegating data validation to programmers was that, if some programmer forgot to check for some constraint, or coded the check wrong, and if the error got through program testing and into production, some poor slob DBA was going to get beaten up by the user community for the failure of the database to adhere to the business rules.

This could occur in situations where the DBA did not even have access to the source code produced by the programmers in order to make the correction, or the compiler needed to convert the programs into runnable form. But the users are still going to beat up on the DBA instead of the programmers, just because they can. This might be all greek to you, if you have only worked with Access. But it's real world stuff, going back 25 years in the world of datbases.

In a lot of situations, what ends up happening is that the programmers do their own validation to begin with, and the database also has the constraint written into it. In this case, constraint checking might put an "extra load on the server" to the tune of maybe 10%, but it's easy for a DBA to live with that extra load. In general, poor physical organization of the data usually imposes an extra load of somewhere around 100% on a database. So if the DBA needs to speed things up, he can perform a physical reorganization that will accomplish that goal, without "breaking" any of the programs that read from and write to the database.

Going back to your situation, it really boils down to two things: coding ease (both schema and VBA code) and user convenience. I'm going to suggest that, if you discover a materially better way to organize your schema that you go back and do that now.

If you don't, you may find that you have to do that six months from now, anyway. At that point, since you are the DBA as well as the only user, the only person you will have to beat up is yourself.

Good luck. Received on Mon Dec 31 2007 - 17:19:45 CET