I have an 'Issue' object/table that represents a problem in our system. I'd like to be able to associate Issue objects with one of 5 different other objects/tables.

One approach would be to have 5 join tables, one per source object/table.

Another approach would be to have my Issues table have 5 reference columns, of which only one is used at a time. Each reference column would be a foreign key to one of the associated 'source' tables.

Neither of these solutions seem optimal to me. Does anyone have an idea on other/better solutions that I haven't considered yet?

There is no other mechanism than a key copy and a foreign key constraint in the relational model, and this is a good thing.

Can you explain what you mean by 'key copy'?

Couldn't you use an <any> mapping? The relational model is not optimal because you need two columns: one indicates the table to join to, the other is the key (but you obviously can't have a foreign key constraint). Check the reference docs.

I'm not sure.

I just re-read section 5.2.5 'Any Type Mappings' in the Hibernate documentation and I'm still not sure what <any/> type mappings are all about (sorry).

It sounds like it *might* provide what I'm looking for, but it also states that "it is impossible to specify a foriegn key constraint for this kind of association". I'm not sure I need a foreign key constraint, it just seems like sound database design practice.

Can someone provide a more throrough definition of the <any/> type mapping, or perhaps an example?

Thanks.

You need an <any> mapping if you have an association where the target is mapped with a table per concret class mapping. Thats all you have to know, if you don't have one of those, don't bother.

Entities in the relational model have not pointer associations, such as in Java, but share a key value. In most cases, this "copy of a key value" is protected with a foreign key constraint, to ensure referential integrity and to protect the semantics of your entity relationships.

So 'key copy' is synonymous with 'foreign key'?

If this is the case, is it reasonable to have multiple foriegn keys in a single table, where only one of which will be used at a time? This means that the other (unused) foreign key columns will be null.

I think you should read up on relational database fundamentals.

And: The <any> is never a good idea, it's a workaround for legacy data models.

Got it.

So either:

o It's standard practice to have multiple PKs but use only one, and I don't know it.

or

o It's a violoation of standard practice to have multiple PKs but use only one, and I don't know it.

Given that I've spent over an hour with the Oracle 9i Complete Reference Manual and can't find one word regarding this issue, can you give me a hint which one you think is correct? :)

what are you talking about? You can't have multiple primary keys. Do you mean multiple foreign keys? And in what sense multiple?? All to the same referenced table? Different tables?

Get a book about database design, not the Oracle manual.

I'm sorry. I don't know how I got PK in there. I certainly meant FK.

Wrong book. That explains it. I'll look elsewhere.

My problem is that I have one table that I need to relate each row to exactly one of four other tables.

The goal is to associate problems within our system to multiple problem-sources.

An <any> mapping is not unreasonable in this case. However, if I were you, I would make the associated objects all belong to a single <subclass> or <joined-subclass> inheritance hierarchy. Much more elegant overall design.