Q: How to relate field in table A to more than one other table

[John]

(Hope I can ask this clearly...)

I have three tables: tblPayments, tblCompanies, and tblPeople.
tblPayments contains a field called lngPayeeID that needs to relate to
records from tblCompanies and tblPeople. Why? Because a "payee"
(lngPayeeID) can be either a company (tblCompany.lngCompanyID) or a
person (tblPeople.lngPersonID).

I know how to relate tblPayments.lngPayeeID to a single table's field,
such as tblCompanies.lngCompanyID. I just can't figure out how to
relate it to two unlike tables (i.e., a payee can be a company or
person, which are both unrelated to each other).

Any suggestions are appreciated!
John

[Fletcher Arnold]

"John" <so*********@hotmail.com> wrote in message
news:90**************************@posting.google.c om...

(Hope I can ask this clearly...)

I have three tables: tblPayments, tblCompanies, and tblPeople.
tblPayments contains a field called lngPayeeID that needs to relate to
records from tblCompanies and tblPeople. Why? Because a "payee"
(lngPayeeID) can be either a company (tblCompany.lngCompanyID) or a
person (tblPeople.lngPersonID).

I know how to relate tblPayments.lngPayeeID to a single table's field,
such as tblCompanies.lngCompanyID. I just can't figure out how to
relate it to two unlike tables (i.e., a payee can be a company or
person, which are both unrelated to each other).

Any suggestions are appreciated!
John

It would be easier for you if you had a rule that payees have to be people.
It would mean that you would have to add a person to each company you wanted
a payment from - but it would not force you to make structure changes.

If, however, you are feeling industrious then you could change your
structure so you have a table of parties which gives a unique ID to each
party (whether company or person). Then both tblCompanies and tblPeople
have a field FkPartyID(indexed no duplicates) so there is a 1-1 relationship
between the tables. In the table of payments you record the party ID for
the payee.

This idea of subclassing parties into either companies or people might suit
you, but it might be overkill for something that you could solve in a lazier
fashion. Let us know what you think.
Fletcher

[John Kreps]

Fletcher, thanks for the reply. The tables (tblPeople and tblCompanies)
must remain unrelated unforunately for me.

Your idea on creating a field/key that would be unique to both tables is
interesting...I'm not sure how I'd implement that tho. How could I
ensure the new field contained unique data without user intervention
(i.e., an autonumber field for two "joined" tables)?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Fletcher Arnold]

"John Kreps" <de***********@kreps.mailsnare.net> wrote in message
news:3f***********************@news.frii.net...

Fletcher, thanks for the reply. The tables (tblPeople and tblCompanies)
must remain unrelated unforunately for me.

Your idea on creating a field/key that would be unique to both tables is
interesting...I'm not sure how I'd implement that tho. How could I
ensure the new field contained unique data without user intervention
(i.e., an autonumber field for two "joined" tables)?

You could have PtyID as an autonumber, PtyName as text. When you add either
a new company or new person you first create an entry in tblParty and get an
ID. This ID is then put into either tblCompanies.FkPartyID or
tblPeople.FkPartyID before filling in the other fields for the selected
table.

I have used this idea in a database I did for a university where all
contacts got an entry in tblPersons, but students had an entry in
tblStudents with a matching PsnID which was also unique in the students
table. This made a certain amount of sense because although all people
share fields like FirstName, LastName, etc students had additional data not
relevant to other types of person. In other words students were modelled as
a sub-class of people (sub-class - what an appropriate prefix ;-)

Obviously, your situation is not quite the same and my gut feeling is that
there might be a better way. It's really hard to say without an overall
view of the database. Here are a few random thoughts:

Allocate all payments to people (Although I might guess, you haven't
actually stated why you wouldn't do this)

Not enforce referential integrity at table level. Let your interface and
coding take care of it.

Split the payments table into PeoplePayments and CompanyPayments and join
with a union query.

Perhaps you will get some input from others...

Fletcher