Oracle-L: Re: Data modeling question

From: <Rick_Cale_at_teamhealth.com>
Date: Wed, 30 Oct 2002 11:06:35 -0800
Message-ID: <F001.004F7D19.20021030110635@fatcity.com>

Jared,

Yeah, It may be just a design issue but not quite sure.

I was referencing from a document "Data Modeling: It's Really All About the Relationships" by Dr. Bert Scalzo of Quest Software. Data modeling is part of the conceptual design phase. When speaking of the E-R methodology, the data model produces 1. E-R diagram which represents the data structures in some type of graphical form. The second component is the data document which details the data objects, relationships, and database rules. Here is where I think it could be argued both a modeling/design issue. I certainly see some merit in your reasoning. I agree the use of surrogate keys would prevent this. I do not know why this was not mentioned by the author.

Rick

                                                                                                      
                    Jared.Still_at_ra                                                                    
                    disys.com            To:     Multiple recipients of list ORACLE-L                 
                    Sent by:              <ORACLE-L_at_fatcity.com>                                      
                    root_at_fatcity.c       cc:                                                          
                    om                   Subject:     Re: Data modeling question                      
                                                                                                      
                                                                                                      
                    10/30/2002                                                                        
                    01:05 PM                                                                          
                    Please respond                                                                    
                    to ORACLE-L

Rick,

This doesn't actually have anything to do with data modeling, but rather, database design.

The FK can't be enforced on a multipart key if one of the columns is null, obviously. At least, I *think* it's obvious.

What happen when you try to update column b in table 2 with a valid value?

with and invalid value?

There's a good case here for using surrogate keys, as always.

That way, invalid values cannot be put in an FK column, since there is only one column needed to reference the foreign PK.

Jared

Rick_Cale_at_teamhealth.com
Sent by: root_at_fatcity.com
10/30/2002 08:08 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc:
        Subject:        Data modeling question

I was reading one of the papers on the quest website about data modeling. An example was presented and I did not believe the results so I tried it out. Of course the writer ( Phd) was correct.

Does anyone know why does not enforce RI constraint if any of the foreign key colums are NULL. I took it literally that if I insert any record in table_2 that a corresponding record must be in the parent(table_1). Apparently this is not so. Someone please explain.

Thanks
Rick

drop table table_2;
drop table table_1;

create table table_1 (
  a number not null,
  b number not null,
  c number,
constraint table_1_pk primary key (a,b));

create table table_2 (
a number,
b number,
d number not null,
e number not null,
f number,
constraint pk_table_2 primary key (d,e), constraint fk_table_2_reference_table_1 foreign key (a,b) references table_1 (a,b))
/

insert into table_2 values(1,NULL,3,4,5);

1 row processed.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Rick_Cale_at_teamhealth.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Rick_Cale_at_teamhealth.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message