Re: An 1-to-1 relation: Is it normal?

Hi Igor,
 the solution you describe is common to solve the problem you encountered.
I've heard of it in the book "Fundamentals of Database Systems" (Elmastry - Navathe) in the chapter ER-to-Relational Mapping.

Here is an idea for you. You can make any DB user unaware of this table splitting, just create a View joining table ONE and table TWO with the "outer join" operator :
SELECT *
FROM ONE O, TWO, T
WHERE O.PERS ID = T.PERS ID (+) This View will be equivalent to the single-table design.

HTH

                                                   
 Luca Minudel                    software designer
 Italy                             Conegliano (TV)
 voice & fax                     +39 (0)438 412280
 e-mail                      luca.minudel_at_nline.it
 WWW                       (italian language used)

Igor Sereda <sereda_at_spb.runnet.ru> scritto nell'articolo <351FFB6C.FFBC9676_at_spb.runnet.ru>...
> Hello,
>
> i am pretty confused by a sort of solution for the folling situation:
> I have a table containing personal information with primary key
 PERS ID.
> There are few fields which are truly required and a lot others which
> would be null in most cases.
> So there's a tempting idea to separate one table into two, so one
 would
> contain NOT NULL fields, indexed by primary key PERS ID,
> and the other will be filled with all other fields, also indexed by
> primary key PERS IS, also functioning ass a foreign key into the first
> table.
>
> +++++
> create table ONE (
> PERS ID number primary key,
> f1 number not null,
> ... <few fields>
> );
>
> create table TWO (
> PERS ID number primary key references (ONE),
> of1 number,
> ... <many fields>
> );
>
> +++++
>
> So what we are getting is a one-to-one-or-zero relation.
> I feel there's something wrong in such a construction, but
> i cannot find out what exactly.
>
> I will appreciate any advise,
>
> Thank you,
> Igor Sereda
>
> --
> Igor Sereda,
> ITC, Russia
>
Received on Mon Mar 30 1998 - 00:00:00 CST