Re: FK-like constraint referencing union of external tables?

From: joel garry <joel-garry_at_home.com>
Date: Wed, 2 Sep 2009 10:07:49 -0700 (PDT)
Message-ID: <e880df91-bac6-4ec0-a81e-92f41e75e89a_at_v15g2000prn.googlegroups.com>



On Aug 22, 5:19 pm, Tim X <t..._at_nospam.dev.null> wrote:
> Mark D Powell <Mark.Pow..._at_eds.com> writes:
>
>
>
> > On Aug 21, 3:42 pm, Ruud de Koter <nob..._at_internet.org> wrote:
> >> Hi Mark,
>
> >> > I have a situation which (simplified) looks like this:
>
> >> > Table UNITS has a PK on UNITS.NAME. (unit name, varchar(12))
> >> > Table DEPTS has a PK on DEPTS.NAME. (dept name, varchar(12))
>
> >> > I want to create a table with column UNIT_NAME_OR_DEPT_NAME, with the
> >> > requirement that data in this column must be either a valid unit
> >> > name or a valid department name.
>
> >> Sounds like you want to use some object-oriented concepts in your
> >> database programming. Apparently there is a supertype of both units and
> >> departments involved. One way to make this work is to sort of
> >> acknowledge the existence of this supertype, and give it its own table,
> >> lets naively call it THINGS. THINGS would then contain columns for the
> >> shared attributes of units and departments, while DEPTS and UNITS would
> >> have columns for the attributes that are unique to either concept.
>
> >> > I tried adding two FK constraints, but found this gave me the
> >> > intersection rather than the union of the two tables.
>
> >> >      CONSTRAINT FOO_FK1 FOREIGN KEY(NAME) REFERENCES UNITS(NAME) ENABLE,
> >> >      CONSTRAINT FOO_FK2 FOREIGN KEY(NAME) REFERENCES DEPTS(NAME) ENABLE,
>
> >> > How can I create a FK or other constraint that would me to reference
> >> > the union of two tables?
>
> >> If you were to introduce a table THINGS, you could easily reference that
> >> table's primary key.
>
> >> Most likely you will reply with the remark that you are not free to
> >> change the data model ;-). In fact, that might be a good thing in this
> >> case, as there are many pittfalls in the combination of object-oriented
> >> thought and relational databases.
>
> >> Regards,
>
> >> Ruud de Koter
>
> > I will just add that in my opinion a relational database should be
> > used to hold relational data in a fully normalized design and that
> > objects belong in the application layer and should not be stored in
> > the database.  Storing objects in the database just results in data
> > extraction and integrity issues in the long run.
>
> > When you come across a situation where you end up trying to define
> > constraints that violate the constraint definition rules you almost
> > surely have non-normalized data.  Developers have a bad habit of
> > working from the application side into the database and trying to
> > impose the application structure on the database data instead of
> > working from the entity attribute level, storing this in the database,
> > and then designing the code logic to construct what the application
> > needs from the data.
>
> > IMHO -- Mark D Powell --
>
> I think you hit the nail on the head here. When I first saw this
> question, two things jumped out at me -
>
> 1. the problem with defining the FK is primarily due to an attempt to
> work with a de-normalised table structure and
>
> 2.What will be the PK for the new table?
>
> I suspect the OP may be in one of those unfortunate, but all too common
> situations where the application they need to modify/extend has a
> denormalised form and now attempting to extend the model while
> maintaining data integrity is going to become very difficult.
>
> Would it be possible to achieve the desired outcome with a view rather
> than through generating another table? this at least might prevent
> another table of denormalised data that is likely to cause problems with
> maintenance down the track (I'd be a little concerned about how the data
> in this new table will be maintained as the data in the parent tables it
> is extracted from get updated etc).
>
> Tim
>
> --
> tcross (at) rapttech dot com dot au

See the paper referenced at http://richardfoote.wordpress.com/2009/08/25/demonizing-foreign-keys-helden/ to possibly be driven insane, if you can make it through.

jg

--
_at_home.com is bogus.
http://www.theage.com.au/technology/security/hackers-break-into-police-computer-as-sting-backfires-20090818-eohc.html
Received on Wed Sep 02 2009 - 12:07:49 CDT

Original text of this message