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

> 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