Re: implement a referential integrity constraint (long)

From: xyzzy <>
Date: Fri, 15 May 2009 00:18:08 -0700 (PDT)
Message-ID: <>

On May 14, 7:18 am, Gene Wirchenko <> wrote:
> "Walter Mitty" <> wrote:
> [snip]
> >However, when I recommend this to you, xyzzy,  I think you will treat my
> >suggestion with the caveat it deserves.  BTW, I'm sorry you're stuck in a
> >database that resembles a bunch of twisty little passages, all different.
>  ^^^^^^^^^
>      Aren't they all the same?  Over and over again.
>      It is hardly that it has never been seen before.
>      For those who have led a sheltered life:
>      Announcing a new beginning for Entity Attribute Value DBMSs: EVE.
> Do not put your constraint handling under one roof when you can apply
> it all over on the eaves.  Applications are for applying. ...  Excuse
> me.  I am getting ill.  You should, too.
> Sincerely,
> Gene Wirchenko
> Computerese Irregular Verb Conjugation:
>      I have preferences.
>      You have biases.
>      He/She has prejudices.

You are right. I was troubled by the method of storing the data but I couldn't quite put my finger on it. When I saw those three letters EVA it sent a shiver down my spine - really! And my immediate reaction was denial too! OK any newbie that wants to know what the fundamental problem is, look up Entity-Attribute-Value model both in this thread and in Wikipedia. Lets focus on ways out rather than just how bad EAV is.

The table Addr_type (ahh... the word 'type') was supposed to store the prompt to be displayed on the screen for that row of the address. My original problem was no validation on user input for those fields. The result? address line 4 is always a country but only if it's an overseas address. Address line 3 is always the state, but only if it's a domestic address. And on and on. What a disaster. I bet the guy that thought of it was pleased with himself. They always are aren't they. All I can say is thank god it's only a few lines in the address and not the whole system.

Perhaps I can create a view based on person_address and addr_type. Hmmm...

Also search all_tables for %_type. Received on Fri May 15 2009 - 09:18:08 CEST

Original text of this message