Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
JavaRanch.com/granny.jsp
The moose likes JDBC and the fly likes forgein keys Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "forgein keys" Watch "forgein keys" New topic
Author

forgein keys

Dan Jackson
Greenhorn

Joined: Jun 12, 2001
Posts: 7
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
Ranch Hand

Joined: Jul 18, 2001
Posts: 49
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
Greenhorn

Joined: Jul 15, 2001
Posts: 6
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 );

Thomas Kyte (tkyte@us.oracle.com) <A HREF="http://asktom.oracle.com/" TARGET=_blank rel="nofollow">http://asktom.oracle.com/</A> <BR>Expert one on one Oracle, programming techniques and solutions for Oracle.<BR>http://www.amazon.com/exec/obidos/ASIN/1861004826/ <BR>Opinions are mine and do not necessarily reflect those of Oracle Corp
Robert Brunner
Ranch Hand

Joined: Jul 18, 2001
Posts: 49
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: forgein keys