forgein keys

[Dan Jackson]

is there a way to let one column have two different forgein keys?
i dont think this is possible...
if this is not possible is it better that create two tables or just one table without the forigen key?
the table just a message table, and that message can either come from a user, or a group.
thank you

[Robert Brunner]

I believe you have hit on one of the instances where Object Databases are superior to relational databases, polymorphism. In the relational world, Columns have a specific datatype, while in the OO world, a column can be a pointer (or ref or whatever) to another type. If you want to do this in a relational world, I can think of as few work arounds.
1) Maybe you can get by with a UDT or REF? This makes the programming more complicated, assuming your database (and JDBC driver supports it).
2) Use an intermediate table, which has three columns, a primary key (which is the new foriegn key in your original table), and a column for group ID and a column for user ID. Then you just follow the ID which is not NULL.

[Thomas Kyte]

Well, I would tend to disagree with the Object database comment as a "pointer" as you call it is really a step backwards somewhat in data integrity (we had that with network databases way back when). You lose the referential integrity rule the user wants. You can "point" to a user or a group but there is nothing stopping the system from deleting that user or group (leaving you with a dangling pointer that points to nothing). We can do that with REFs and UDTs as you point out but you have that nasty dangling ref issue.
In a pure relational world (as would be true in an OO world), the data model would involve some super type (suppose we call it "ENTITY"). The USER table would be a child table of ENTITY as would the GROUP table. Now the MESSAGE_TABLE has a foreign key to ENTITY (as the OO model would have a pointer to an ENTITY instance but the pointer might be the USER or GROUP as ENTITY is probably an abstract class)..
So, one method to solve this is to have tables such as:
create table entity( id int primary key, data varchar2(10) );
create table users
( id primary key references entity, other_data varchar2(10) );
create table groups
( id primary key references entity, other_data varchar2(10) );
create table messages
( msg_id int primary key, who_from references entity );

[Robert Brunner]

Originally posted by Thomas Kyte:
Well, I would tend to disagree with the Object database comment as a "pointer" as you call it is really a step backwards somewhat in data integrity (we had that with network databases way back when). You lose the referential integrity rule the user wants. You can "point" to a user or a group but there is nothing stopping the system from deleting that user or group (leaving you with a dangling pointer that points to nothing). We can do that with REFs and UDTs as you point out but you have that nasty dangling ref issue.
In a pure relational world (as would be true in an OO world), the data model would involve some super type (suppose we call it "ENTITY"). The USER table would be a child table of ENTITY as would the GROUP table. Now the MESSAGE_TABLE has a foreign key to ENTITY (as the OO model would have a pointer to an ENTITY instance but the pointer might be the USER or GROUP as ENTITY is probably an abstract class)..
So, one method to solve this is to have tables such as:
create table entity( id int primary key, data varchar2(10) );
create table users
( id primary key references entity, other_data varchar2(10) );
create table groups
( id primary key references entity, other_data varchar2(10) );
create table messages
( msg_id int primary key, who_from references entity );

Hi Thomas,
Its all fine to disagree, but be sure your facts are straight. OODBMS often provide referential integrity as a user option. You can propogate behaviors along links however you wish, including locking options, copying behavior (shallow vs. deep), delete behaviors, etc. Thus your criticism is completely unfounded.
The reason OODBMS did not succeed had little to do with the quality of the products, and a lot to do with the strength of the RDBMS vendors and the lack of an OODB standards.
CHeers,
Robert