PDA

View Full Version : Handling multi-foreign key requirements

ddomoney
07-16-02, 17:04
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.
r937
07-17-02, 00:45
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

address_fk
person_fk
corporation_fk
contact_fk

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...


rudy
http://rudy.ca/
r937
07-17-02, 00:52
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....)
ddomoney
07-17-02, 02:02
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).