Why is database integrity so impopular ?

On Oct 14, 12:17 am, DBMS_Plumber
wrote:

> If you mandate that a column cannot contain a NULL, setting a DEFAULT
> means that when a programmer legitimately doesn't have a value for the
> column they aren't obliged to put in there the first thing that
> springs to mind.

How can a programmer legitimately not have a value for the column?
Doesn’t that imply that the schema is inadequate (for supporting
partial information)?
 >> Stay informed about: Why is database integrity so impopular ? 

Jon Heggland wrote:
> DBMS_Plumber wrote:
>> Later, over a cocktail or two, we chatted. In his shop EVERY table
>> has a declared primary key, every column without NOT NULL and DEFAULT
>> must have a documented reason for the deviation.
>
> It is considered a deviation not to have a default? Why is that? I would
> have thought that having defaults for every column would encourage
> sloppiness.

When it comes to "every column", no argument, but isn't what SQL calls a
DEFAULT really just a special form of constraint?
 >> Stay informed about: Why is database integrity so impopular ? 

DBMS_Plumber wrote:
> If you mandate that a column cannot contain a NULL, setting a DEFAULT
> means that when a programmer legitimately doesn't have a value for the
> column they aren't obliged to put in there the first thing that
> springs to mind.

I would have thought the effect was the opposite: If programmers (or
anyone else, of course---I consider myself a programmer:) aren't obliged
to specify a value (and thus aren't required to understand what the
column is for), they will happily go with a default that may very well
be inappropriate/incorrect. But without empirical evidence, either
hypothesis is just speculation.

> I'm sure there would be cases where you could say NOT NULL but not
> provide a DEFAULT. The point to me was that this shop is serious about
> their integrity and this seriousness is manifested in their
> engineering policies and procedures.

Point taken, of course.
--
Jon
 >> Stay informed about: Why is database integrity so impopular ? 

On Oct 6, 12:51 pm, Daniel Pitts
wrote:
> wrote:
> > Hello,
>
> > When time comes to build transactional databases (as opposed to data
> > wharehouses), I belong to the school that STRONGLY believe in
> > normalizing data with high integrity mechanisms. I know all the
> > performance cons but IMHO, pros largely overwhelme.
>
> > It amazes me, though, how many systems rely on the application to
> > manage data integrity. I work as IT director for a large-size
> > manufacturer and *none* of our applications use integrity. And I am
> > talking here of ERP and other mission-critical systems.
>
> > In fact, I had rarely open a database properly normalized and
> > inforced ... and I have been working with databases for over 10 years,
> > mostly in sectors where lack of integrity can result in dramatic
> > consequences.
>
> > What is wrong with modern DB design approaches? And what's the point
> > of using a big relational DB without the benefits of integrity and
> > normalization?
>
> > Thank you,
> > EBL
>
> I think that part of the problem is DB design and Application design are
> really different types of abstraction. For application programmers,
> dealing with DB constraints is tedious.
>
> The truth is that whenever your "Application" calls for persistence, it
> is no longer just an "Application"; it has become a "System". System
> design is a higher level abstraction.
>
> Moving from Application design to System design is /almost/ a natural
> progression, and many engineers traverse the barrier without ever
> realizing and without learning the other aspects of System design. This
> includes learning proper DB design.
>
> I admit that I fell into that category for some time. My background has
> been Application design, but I've started to appreciate the concept of
> constraints at ever level of the System. I even sometimes wish that the
> DB could do more validation than it does, even if it makes things a
> little more "tedious". In this case, tedious just means the hard
> problem is already solved.
>
> --
> Daniel Pitts' Tech Blog: <http://virtualinfinity.net/wordpress/>

I think that relational database theory is too limitting for some
applications. I believe that the modern database needs to be split
into component parts so that not everyone has to be saddled with the
relational part.
 >> Stay informed about: Why is database integrity so impopular ? 

On Oct 5, 5:56 pm, Alfredo Novoa wrote:
> Hello Eric,
>
> ...
>
> The problem is not in modern database theory, the problem is that most
> developers don't know the foundations of their profession and common
> sense is very uncommon.
>
> In my country most people who develop business systems never read a
> database theory book. The few ones who studied a database course in
> the university never understood database theory very well at all, and
> they forgot almost everything just on the end of the final
> examination.
>
> The textbook we used didn't have any chapter devoted to database
> integrity, only a few pages about the poor SQL declarative integrity
> support, and not covered with exercices. The whole Relational Model
> was dispatched in five hours or so, and taught with many mistakes and
> misconceptions.
>
> The vendor's training materials are usually even worse.
>
> And we also have all that abject oriented programming stuff saying
> that RDBMS's are nothing but silly and cumbersome register buckets.
>
> It is not only integrity and normalization. Most developers I know are
> not able to write non trivial queries and they load the data in the
> applications using simple queries, make several iterations on the
> registers, and send the data back to the DBMS.
>
> In the business software industry, technical incompetence is the norm,
> and the develpment tools we have are awful.
>
> Regards

My complaint is that there's too much work to be done and not enough
money to pay for the more expensive relational purists to implement
stuff. What the industry needs is to split up the database into
components so that implementers can decide how to persist their
applications. Sometimes you just want to write data reliably without
worrying about what normalization level you're at.

If and when a project needs fully relational recordkeeping, this
should be available too for those projects that can budget for it.
 >> Stay informed about: Why is database integrity so impopular ? 

wrote:
> On Oct 6, 12:51 pm, Daniel Pitts
> wrote:
>> wrote:
>>> Hello,
>>> When time comes to build transactional databases (as opposed to data
>>> wharehouses), I belong to the school that STRONGLY believe in
>>> normalizing data with high integrity mechanisms. I know all the
>>> performance cons but IMHO, pros largely overwhelme.
>>> It amazes me, though, how many systems rely on the application to
>>> manage data integrity. I work as IT director for a large-size
>>> manufacturer and *none* of our applications use integrity. And I am
>>> talking here of ERP and other mission-critical systems.
>>> In fact, I had rarely open a database properly normalized and
>>> inforced ... and I have been working with databases for over 10 years,
>>> mostly in sectors where lack of integrity can result in dramatic
>>> consequences.
>>> What is wrong with modern DB design approaches? And what's the point
>>> of using a big relational DB without the benefits of integrity and
>>> normalization?
>>> Thank you,
>>> EBL
>> I think that part of the problem is DB design and Application design are
>> really different types of abstraction. For application programmers,
>> dealing with DB constraints is tedious.
>>
>> The truth is that whenever your "Application" calls for persistence, it
>> is no longer just an "Application"; it has become a "System". System
>> design is a higher level abstraction.
>>
>> Moving from Application design to System design is /almost/ a natural
>> progression, and many engineers traverse the barrier without ever
>> realizing and without learning the other aspects of System design. This
>> includes learning proper DB design.
>>
>> I admit that I fell into that category for some time. My background has
>> been Application design, but I've started to appreciate the concept of
>> constraints at ever level of the System. I even sometimes wish that the
>> DB could do more validation than it does, even if it makes things a
>> little more "tedious". In this case, tedious just means the hard
>> problem is already solved.
>>
>> --
>> Daniel Pitts' Tech Blog: <http://virtualinfinity.net/wordpress/>
>
> I think that relational database theory is too limitting for some
> applications. I believe that the modern database needs to be split
> into component parts so that not everyone has to be saddled with the
> relational part.

No offence, but I think you are way off-base to associate any version of
the rm as being aimed at transactions in any way, neither theory (that's
being charitable as much of the transaction writing has no formal
theory) has much connection with other, at least so far. Suspect such
thought has to do with marketing bumpf from various product publicizers,
indoctrination seems to be involved. Still, I must say that a
declarative model of some version of transaction interests me though
I've never seen anybody try to explain one. But so far, you haven't
even hinted at that notion as far as I can tell. I think the happy
cirsumstance is all-important when trying to transpose one technique to
a new purpose and the first insight must be to recognize the circumstance.
 >> Stay informed about: Why is database integrity so impopular ? 

paul c wrote:

> wrote:
>
>> On Oct 6, 12:51 pm, Daniel Pitts
>> wrote:
>>
>>> wrote:
>>>
>>>> Hello,
>>>> When time comes to build transactional databases (as opposed to data
>>>> wharehouses), I belong to the school that STRONGLY believe in
>>>> normalizing data with high integrity mechanisms. I know all the
>>>> performance cons but IMHO, pros largely overwhelme.
>>>> It amazes me, though, how many systems rely on the application to
>>>> manage data integrity. I work as IT director for a large-size
>>>> manufacturer and *none* of our applications use integrity. And I am
>>>> talking here of ERP and other mission-critical systems.
>>>> In fact, I had rarely open a database properly normalized and
>>>> inforced ... and I have been working with databases for over 10 years,
>>>> mostly in sectors where lack of integrity can result in dramatic
>>>> consequences.
>>>> What is wrong with modern DB design approaches? And what's the point
>>>> of using a big relational DB without the benefits of integrity and
>>>> normalization?
>>>> Thank you,
>>>> EBL
>>>
>>> I think that part of the problem is DB design and Application design are
>>> really different types of abstraction. For application programmers,
>>> dealing with DB constraints is tedious.
>>>
>>> The truth is that whenever your "Application" calls for persistence, it
>>> is no longer just an "Application"; it has become a "System". System
>>> design is a higher level abstraction.
>>>
>>> Moving from Application design to System design is /almost/ a natural
>>> progression, and many engineers traverse the barrier without ever
>>> realizing and without learning the other aspects of System design. This
>>> includes learning proper DB design.
>>>
>>> I admit that I fell into that category for some time. My background has
>>> been Application design, but I've started to appreciate the concept of
>>> constraints at ever level of the System. I even sometimes wish that the
>>> DB could do more validation than it does, even if it makes things a
>>> little more "tedious". In this case, tedious just means the hard
>>> problem is already solved.
>>>
>>> --
>>> Daniel Pitts' Tech Blog: <http://virtualinfinity.net/wordpress/>
>>
>> I think that relational database theory is too limitting for some
>> applications. I believe that the modern database needs to be split
>> into component parts so that not everyone has to be saddled with the
>> relational part.
>
> No offence, but I think you are way off-base to associate any version of
> the rm as being aimed at transactions in any way, neither theory (that's
> being charitable as much of the transaction writing has no formal
> theory) has much connection with other, at least so far. Suspect such
> thought has to do with marketing bumpf from various product publicizers,
> indoctrination seems to be involved. Still, I must say that a
> declarative model of some version of transaction interests me though
> I've never seen anybody try to explain one. But so far, you haven't
> even hinted at that notion as far as I can tell. I think the happy
> cirsumstance is all-important when trying to transpose one technique to
> a new purpose and the first insight must be to recognize the circumstance.

Paul, please, who cares about ignorant opinions that have been asserted
with no support whatsoever? So the guy boldly steps forward to show off
his ignorance. Do you really have to reply to it?
 >> Stay informed about: Why is database integrity so impopular ? 

Bob Badour wrote:
....
> Paul, please, who cares about ignorant opinions that have been asserted
> with no support whatsoever? So the guy boldly steps forward to show off
> his ignorance. Do you really have to reply to it?

You're probably right.
 >> Stay informed about: Why is database integrity so impopular ? 

On Oct 31, 5:07 pm, paul c wrote:
> wrote:
> > On Oct 6, 12:51 pm, Daniel Pitts
> > wrote:
> >> wrote:
> >>> Hello,
> >>> When time comes to build transactional databases (as opposed to data
> >>> wharehouses), I belong to the school that STRONGLY believe in
> >>> normalizing data with high integrity mechanisms. I know all the
> >>> performance cons but IMHO, pros largely overwhelme.
> >>> It amazes me, though, how many systems rely on the application to
> >>> manage data integrity. I work as IT director for a large-size
> >>> manufacturer and *none* of our applications use integrity. And I am
> >>> talking here of ERP and other mission-critical systems.
> >>> In fact, I had rarely open a database properly normalized and
> >>> inforced ... and I have been working with databases for over 10 years,
> >>> mostly in sectors where lack of integrity can result in dramatic
> >>> consequences.
> >>> What is wrong with modern DB design approaches? And what's the point
> >>> of using a big relational DB without the benefits of integrity and
> >>> normalization?
> >>> Thank you,
> >>> EBL
> >> I think that part of the problem is DB design and Application design are
> >> really different types of abstraction. For application programmers,
> >> dealing with DB constraints is tedious.
>
> >> The truth is that whenever your "Application" calls for persistence, it
> >> is no longer just an "Application"; it has become a "System". System
> >> design is a higher level abstraction.
>
> >> Moving from Application design to System design is /almost/ a natural
> >> progression, and many engineers traverse the barrier without ever
> >> realizing and without learning the other aspects of System design. This
> >> includes learning proper DB design.
>
> >> I admit that I fell into that category for some time. My background has
> >> been Application design, but I've started to appreciate the concept of
> >> constraints at ever level of the System. I even sometimes wish that the
> >> DB could do more validation than it does, even if it makes things a
> >> little more "tedious". In this case, tedious just means the hard
> >> problem is already solved.
>
> >> --
> >> Daniel Pitts' Tech Blog: <http://virtualinfinity.net/wordpress/>
>
> > I think that relational database theory is too limitting for some
> > applications. I believe that the modern database needs to be split
> > into component parts so that not everyone has to be saddled with the
> > relational part.
>
> No offence, but I think you are way off-base to associate any version of
> the rm as being aimed at transactions in any way, neither theory (that's
> being charitable as much of the transaction writing has no formal
> theory) has much connection with other, at least so far. Suspect such
> thought has to do with marketing bumpf from various product publicizers,
> indoctrination seems to be involved. Still, I must say that a
> declarative model of some version of transaction interests me though
> I've never seen anybody try to explain one. But so far, you haven't
> even hinted at that notion as far as I can tell. I think the happy
> cirsumstance is all-important when trying to transpose one technique to
> a new purpose and the first insight must be to recognize the circumstance.


Maybe the A of ACID. For instance, what would I use if I want every
record to store or none, but otherwise wasn't too picky? It could be a
filesystem question I guess.

Look at the web market for instance, every day I run across a web
application that has lost contact with its sql server way before the
webserver has lost contact with its visiting browser. I'm just saying,
sometimes you really want every clock cycle you can get and still want
atomicity of your persistance state (well, maybe could be just me). If
you don't think relational integrity of your data model burns oil then
maybe, but in that case, show me the money and the rdbms that I can
install on my very marginal rented webspace.
 >> Stay informed about: Why is database integrity so impopular ? 

On Nov 1, 8:57 am, "Walter Mitty" wrote:
> wrote in message
>
>
>
> > I think that relational database theory is too limitting for some
> > applications. I believe that the modern database needs to be split
> > into component parts so that not everyone has to be saddled with the
> > relational part.
>
> I think you might have meant "the relational data model" where you wrote
> "relational database theory". True?
>
> Could you give an example?
>
> If you design part of your database to be relational, and another part to be
> something else that's not conformant to relational, how do you use data
> from the two parts together in an integrated fashion? Or is that too much
> to ask?


Well in theory everything I want could be handled by a relational
database and normalized tables. In practice, some situations are too
expensive to be normalized for me as currently implemented. The ideal
situation would be for my database product to offer fully normalized
looking access to what are essentially repeating groups or cached
direct access, ie., if I could have 'select whatever from wherever
where key = someinteger' be actually and guaranteed translation to
'return whereever[someinteger * elementsize]' then sure, I'll just put
up with a wordy dialect that translates to an array reference.
Sometimes I want to be that precise in how many computer cycles I've
decided to burn for that particular operation. I'm a convenience store
clerk with rented webspace for my forum for instance. I'm spamming
digg with my relational dbms using blog entry and I don't want my $10
a month server to be 'buried'.

Yes, I have this for ram, but can I set array elements 1, 3, and 5 to
value 'something'' and have all three assignments either be persisted
or not and no partial state? Can todays rdbms offer me this without
even using a hash, just indexing into adjacent locations of storage?
Could I do it with sql today, passing an out of band pragma that says
when I'm describing a normalized table that replaces the repeating
groups I loved so much, can I still tell it that "I'd really like this
puppy to be at random access speed into contiguous storage localized
in reference to the current left side of the join?"
 >> Stay informed about: Why is database integrity so impopular ? 

wrote in message


> I think that relational database theory is too limitting for some
> applications. I believe that the modern database needs to be split
> into component parts so that not everyone has to be saddled with the
> relational part.

I think you might have meant "the relational data model" where you wrote
"relational database theory". True?

Could you give an example?

If you design part of your database to be relational, and another part to be
something else that's not conformant to relational, how do you use data
from the two parts together in an integrated fashion? Or is that too much
to ask?
 >> Stay informed about: Why is database integrity so impopular ? 

On Nov 1, 8:57 am, "Walter Mitty" wrote:
> wrote in message
>
>
>
> > I think that relational database theory is too limitting for some
> > applications. I believe that the modern database needs to be split
> > into component parts so that not everyone has to be saddled with the
> > relational part.
>
> I think you might have meant "the relational data model" where you wrote
> "relational database theory". True?
>
> Could you give an example?
>
> If you design part of your database to be relational, and another part to be
> something else that's not conformant to relational, how do you use data
> from the two parts together in an integrated fashion? Or is that too much
> to ask?

This is probably where I proposed ditching relational theory
inadvertently. I think there is a role for relational theory, just
because I'm not immersed into it at the level others are doesn't mean
I'm telling _them_ to ditch it.

I'm saying that I could think of cases where I would avoid relational
technology for particular benefits, chiefly in terms of certain costs
of running it.
 >> Stay informed about: Why is database integrity so impopular ? 

On Oct 5, 11:30 am, wrote:
> Hello,
>
> When time comes to build transactional databases (as opposed to data
> wharehouses), I belong to the school that STRONGLY believe in
> normalizing data with high integrity mechanisms.  I know all the
> performance cons but IMHO, pros largely overwhelme.
>

Just to be difficult.....

This is now a long thread, with numerous well-crafted arguments
exhaustively
defending good database design. But (largely for the sake of
argument)
there are times when bad design is called for.

For large, well-funded projects, performance can always
be purchased, usually for far less than the maintenance costs
associated with
bad design.

But smaller, not-so-well funded systems are part of the bell curve
too.
I wrote a web-based administration system for an "outfitters"
organization once.
There were about 1000 licensed outfitters that needed to be tracked
by a single-person administrator. So there was a private interface
plus a
public one. The public interface was supposed to
include a keyword search that combined outfitter location with
arbitrary
combinations of attribute keywords like "elk hunting, fly fishing,
walleye, pheasant" etc.

This organization had a limited budget and insisted on
a cheap shared host server. Mysql queries with mulitiple named
cursors
ran like lightning on my desktop linux box, but they ran like half-
frozen molasses
on the overloaded shared host server.

But application code that did "string-like" over a long string of
comma-separated
keywords worked, and it ran an order of magnitude faster (on the
chintzy shared
host).
 >> Stay informed about: Why is database integrity so impopular ?