Re: Database Design: Storing a UNION in the database

Hi,

try subtyping:

	YOUR_ENTITY
	_________________________
        | ID                    |
        |-----------------------|
        |TYPE                   |
        _________________________


                    |

                    |

                    O TYPE
                   ---
                   | |
          +--------+ +-----------------+	
 _________|_____________   ____________|__________
| ID                    | |ID                     |
|-----------------------| |-----------------------|
|EMPLOYEE_ID            | |EMPLOYER_ID            |
|_______________________| |_______________________|
        

Resulting in 3 tables where ID is primary key in table 1 and primary key as well as foreign key in the tables 2 and 3.

Ashwin Kumar schrieb:

> 
> Hi All,
> 
> I am struggling here to figure out how to map a 'C' Union like structure
> into the
> database. In my ER-like modeling, it is mapped as a inheritance - This
> is what
> I want to store in the database :
> 
> ID  // this is the primary id or the primary key
> TYPE // this could be 1 or 2
> EMPLOYEE_ID // this is  a foriegn key into the emplpoyee table
> EMPLOYER_ID // this is a foriegn key into the employer table
> 
> --- Now - the problem is that EMPLOYEE_ID is only valid when TYPE is 1
> and EMPLOYER_ID is only valid when TYPE is 2.  Thus, when TYPE is 1,
> EMPLOYER_ID could be any junk, including 0.
> 
> How do I map this into Oracle (8E) ???
> 
> Looking for comments and help,
> 
> TIA,
> 
> Ashwin
> AshwinKumar_at_cheerful.com
> 
>                                                   ------------------------------------------------------------------------
> 
>   Ashwin Kumar <ashwinkumar_at_cheerful.com>
>   Software Engineer
>   Cisco Systems
> 
>   Ashwin Kumar
>   Software Engineer                                      <ashwinkumar_at_cheerful.com>
>   Cisco Systems
>   130 W Tasman Drive SJ-B2 #285A ;San Jose;CA;95135;USA  Fax: (408)526-8282
>                                                          Geschäft: (408)527-7622
>                                                          Netscape Conference-Adresse
>                                                          DLS-Server von Netscape Conference
>   Weitere Informationen:
>   Nachname     Kumar
>   Vorname      Ashwin
>   Version      2.1
 

-- 

Regards

Matthias Gresz    :-)


Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm