Results 1 to 4 of 4

Thread: Shared table

  1. #1
    Join Date
    Aug 2004
    Location
    Croatia
    Posts
    4

    Shared table

    I have two main tables <Persons> with PersID PK and <Services> with ServID PK and aditional "shared table" <Addresses> intended for saving addresses for both Persons and Services. Addresses table has compound primary key (ItemID,Type,No) where ItemID is ID of Person or Service and Type is discriminator ['P','S'] definig if this address in Addresses table belongs to Person or Service. Each Person/Service could have more addresses what is defined by No part of compound PK. So there are one-to-many relationships between Person/Service and Addresses tables.

    The quesion is how to create referential integrity constraint for this Addresses table? For example if I want cascade delete of Person with all its addresses from Addresses table?

  2. #2
    Join Date
    Aug 2004
    Location
    Croatia
    Posts
    4
    To be more clear:

    simple one-to-many case with one table:

    Persons(ID,attributes) ... ID=PK
    PersAddresses(ID,NO,attributes) ... (ID,NO)=PK

    Services(ID,attributes) ... ID=PK
    ServAddresses(ID,NO,attributes) ... (ID,NO)=PK

    Now I want to compound PersAddresses and ServAddresses into one table:

    Addresses(ID,TYPE,NO,attributes) ... (ID,TYPE,NO)=PK

    How to create integrity constraint since now PK points on two tables?

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    You cannot define a foreign key that references 2 parent tables, as you have tried to do.

    There are 2 approaches you could use:
    - subtypes
    - arcs

    Subtypes: Persons and Services are both subtypes of a supertype, e.g. Entities:

    create table entities (entity_id int primary key, entity_type ... );
    create table persons (entity_id int primary key references entities, ... );
    create table services (entity_id int primary key references entities, ... );
    create table addresses (entity_id references entities, address_no int, ...
    primary key (entity_id, address_no));

    Arcs: Address has 2 mutually exclusive foreign key constraints:

    create table persons (person_id int primary key, ... );
    create table services (service_id int primary key, ... );
    create table addresses (address_id int primary key, person_id references persons, service_id references services,
    check ((person_id is not null and service_id is null) or (person_id is null and service_id is not null)));

  4. #4
    Join Date
    Aug 2004
    Location
    Croatia
    Posts
    4
    To be more concrete I share the Addresses table among four entities (persons,organizations,inventories,services). Each of these entities can have more than one address in Addresses table. Using one field (column) TYPE ['P','O','I','S'] I can define to which entity a particular address in Addresses table belongs. But I do not have referential constaint and therefore cannot implement cascade delete feature (what is my intention).

    If I want to use Arc, do I have to have four additional fields (mutually exclusive) in my Addresses table (PersID,OrgID,InvID,ServID,No) without TYPE discriminator?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •