Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Winnipeg, Canada

    Cool Handling multi-foreign key requirements

    I am currently creating a datamodel for a large web based application and have a problem that I would like to bounce off some others.....

    BTW I am using postgresql 7.2.1

    I have some tables ie: address that I am trying to create generically but am probably trying to do too much with. For example I am trying to have the address table relate to people or organizations or potentially appointments.

    Table structure (now):
    address_id serial primary key
    address_type_id int foreign key
    person_id int foreign key *only tagging locations to people*
    address1 text
    address2 text
    address3 text
    postcode varchar
    city_id int foreign key
    province_id int foreign key
    country_id int foreign key

    Potential solutions:
    1) add foreign keys for organization_id & appointment_id - how do I create an integrity constraint that says one of the foreign keys must be non-null?

    2) create three inherited tables ie: person_address, organization_address, appointment address with the distinctive trait being the foreign key. How much complexity is this going to add to my database queries that are being dynamically created using PHP???

    3) Others???

    Note: I am a little unsure if I really want to use an address - organization linkage as I stating in my business rules that an organization is represented by a person.... but I definately need to be able to handle appointment addresses.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada

    enlightened (heh) former ERwin user

    don't create separate address tables, it's too messy

    remove the foreign key person_id, and create an "address_owned" table with these columns


    notice that this moves the "one of these must not be null" problem out of the address table but doesn't solve it

    in fact it compounds the problem, because you also now need some uniqueness checking, to prevent an address from being shared, because... whoa, wait a sec, that might not be a bad idea, eh?

    so address_owned is a many-to-many intersection table, except that on one side of the relationship, it has three owner tables instead of one

    going from an owner to get its address(es), you'd use one of three similar queries, each a simple inner join from one of the three owner tables through address_owned to the address table

    going from a specific address, getting its owner is a union of three inner joins, because you don't know in advance which of the three foreign keys to the other side of the m-m will be null -- but you can see now how you could have both a company and one or more people all at the same address, which is probably quite realistic, eh?

    so anyhow, the sql is really straightforward, your table design is simple, and your data is as unredundant as possible

    i don't think you need to worry about "one of these must not be null" conditions in a database constraint because your application program to add new addresses can easily have a simple check in it to prevent adding an address that doesn't belong to any person, corporation or appointment...

    hope that helps

    next you're gonna want to relate people to corporations, aren't you...


  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    p.s. you probably don't need address_type foreign key, not if it was supposed to distinguish people from corporations from appointments...

    as for whether cities should be in a separate table, that's debatable -- but in a different thread ;o)

    country_id, eh? how worldwide does this address table have to be?

    (it'd be nice to see american states stored in a province field for a change....)

  4. #4
    Join Date
    Jul 2002
    Winnipeg, Canada

    More thoughts

    My intent with the address type is to make the app as generic as possible. ie: Is the address for home, business, cottage, etc. (address_type).

    I know that there are some practicle concerns here but the situation is as follows:

    The app is being written for a specific client (a non-profit) with grant money. They are hoping to recoup the development costs using resale of the app to other organizations. Some of these organizations may be in the US , etc. I am really trying to balance client needs vs making the app saleable.

    My hope is to be able to capture multiple (unlimited) addresses per person. That principle extends to phone numbers and Email as well. I don't think that I am out of line using the rule that an organization can exist only if represented by a person. This is a natural extension of the fact that this is a resource scheduling and reporting tool with the trait that it captures who requested the resources (person -> from organization) as they payor for the resource.

    We also need to capture data on where the resource is going to be used (appointment location).

Posting Permissions

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