RE: [agileDatabases] Maximum length constraints for agility
>We should consider whether we would want the restrictions of an input device to dictate database-level constraints. We might also consider that
>today's typical screen entry or data exchange has no such issues. Whilescreen size is still a factor for designers, this technology requirement need not >whittle down our original business requirements. I feel comfortable relegating this reason for a maxlen to the past.
Relational databases attempt to implement a very fundamental and worth-while (in my opinion) objective. Their objective is to protect the integrity of what is often the life-blood of an organization: Its data. The goal of normalization, whether it be 1NF,2NF,3NF or 4NF, is the same: Minimize the chances of “bad” or incorrect data from making it’s way into your database.
An institutions data can and often does come from varied and disparate sources. These sources consist of internal data entry systems, external electronic interfaces, EDI or XML exchanges, just to name a few. As a result, many (if not most) databases contain data that originated from more than one application. In addition, these databases often find themselves integrated with and exchanging data with older “less constrained” data repositories. Ensuring this data meets some minimum standards of quality before “admitting” it into your RDBMS database is what database constraints are all about. A field length constraint is just one type of such a constraint. If you’re willing or even eager, to eliminate the field length constraint, are you comfortable relying on each of these external data sources to determine what a makes valid postal code? For example, a US zip code is usually modeled and constrained to digits. It should be at least 5 digits long and may be as long as 9 depending on whether you’re using two fields or one. What would be the point in removing a length constraint of this nature? I suppose if zip codes ever grew to 20 digits you wouldn’t need to change your data model. My experience has been that such changes are far more rare than feature driven database changes. Usually it’s changes like “We want to support multiple mailing addresses in the next release of Appwidget”.
>Where should the logical maxlen be placed in a logical data model? Given that all UI and web service validation routines need access to this >constraint information, it can be made available by being captured as other business rules might be, in the database itself.
>I suggest that the database proper (rather than the schema) is a good place for specifying all business constraints. There must then be enforcement of >the use of standard CRUD services that employ such constraint logic for all software maintaining the database.
If I understand you correctly, you’re suggesting that max length constraints be persisted in a user-defined business table rather than the schema itself. By doing this, you’re re-implementing something that has already been done! The databases underlying “catalog” or schema definition consists of tables that define the rules by which data is either rejected or accepted into the database. These rules are easily configured and entered into the database through DDL statements.
It’s about separation of responsibility and I’m of the opinion that a database ought to protect itself from “bad data”.
>I would venture that the application of the maxlen in places where the proposition should have no such constraint, in addition to the tight coupling >between the logical and physical maxlen, has cost the industry and individual organizations a bundle. It is but one area related to constraint handling >where we have been bleeding, but one about which I hear little mention—and no noise, no fix, I'm guessing.
I think there’s no noise because there’s no problem. Length constraints are one way to ensure valid data. Just because a field might to turn out to require 20 bytes instead of 10 doesn’t invalidate the entire concept of a length constraint. It simply means the constraint was implemented while lacking sufficient information. Increasing it from 10 to 20 bytes is a 5 second operation (up to 4000 bytes), at least in Oracle.
>Relational theory is predicate logic and set theory. Relational theory does not speak to maxlen, but every implementation of the relational model >leads developers to make extensive use of such.
Because RDBM’s are concrete implementations of theory. While it’s true that perfect data quality can be achieved in theory, concrete implementations require constraints.
From: agileDatabases@yahoogroups.com [mailto:agileDatabases@yahoogroups.com] On Behalf Of Dawn Wolthuis
Sent: Thursday, July 27, 2006 5:54 PM
Subject: [agileDatabases] Maximum length constraints for agility
I just read through much of Database Refactoring and gave a nod to it
(amazon link and all) in my latest blog entry (blatant ad coming up)
entitled "To Whom Should Size Matter?" at
http://www.tincat- group.com/ mewsings
The book is great! It does an excellent job in assisting sites with
implementations using existing SQL-DBMS products. This is an
important contribution to an aspect of the industry that seems to have
become less agile over the past couple of decades.
My goals include nudging the industry toward more agile DBMS tools,
which likely means moving beyond SQL, in my opinion. This is perhaps
a Don Quixote effort on my part, but there have definitely been steps
made in this direction by companies such as Microsoft, Oracle, and IBM
since the introduction of XML, for example.
I'm particularly interested in whether those working on agility with
current DBMS's share or disagree with my perspective on the size
matter addressed in this blog entry. Thanks in advance to anyone who
can give it a read. Comments on the blog itself or in this forum are
welcome. Cheers! --dawn
Dawn M. Wolthuis
Tincat Group, Inc.
Take and give some delight today
> By this argument, since Java doesn't come withThe developers of recent versions of Java JVM and .Net CLR have worked
> out-of-the-box implementation of the Singleton
> pattern, and everyone who wants a Singleton has to
> code it themselves, and these implementations can (and
> usually are) different and incompatible, then an
> implementation of the Singleton design pattern should
> be added to Java.
> This is absurd. Just because a feature is desired by
> a large subset of the users does not automatically
> mean that that feature should be added to the common framework.
very hard so that the double-checked-locking implementations of
Singleton actually work properly.
So they did work to fulfill the desires of a large subset of users.
Not "automatically", but as a reasonable approach to help people
C. Keith Ray