DB Struct/Relationships Issue: Where To Add Table?

[(PeteCresswell)]

I've got

tblDeal ==> tblTrustee ==> tblTrusteeContact

Each deal can have one and only one trustee.

Each trustee can have many contacts.

VIZ: http://tinyurl.com/2rzosd


User wants to be able to assign a single (one and only one....) trustee contact
to a given tblDeal row.

My kneejerk is to just add a new field: tblDeal.TrusteeContactID and enforce
the relationship to tlkpTrustee programmatically.

But somehow this doesn't sound like the good-right-and-holy path to me.

Can anybody suggest a solution that is technically more defensible in the
context of good database design.

 

[(PeteCresswell)]

Per (PeteCresswell):

VIZ: http://tinyurl.com/2rzosd

If you click "All Sizes", then choose "Original" it's readable.

 

[Roger Carlson]

Each deal can have one and only one trustee. But presumably each trustee
can be assigned to more than one Deal. Therefore, you have a one-to-many
relationship with tblDeal being on the Many-side. So you would want to
place the primary key of tblTrustee (TrusteeID?) in tblDeal as a foreign key
and create a relationship. No need to programmatically maintain referential
integrity because the relationship will do that automatically.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

Sorry, I didn't look at the picture. As it stands, you already can only
assign one trustee to any particular Deal. Or am I missing something?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[(PeteCresswell)]

Per Roger Carlson:

Sorry, I didn't look at the picture. As it stands, you already can only
assign one trustee to any particular Deal. Or am I missing something?

The Deal==>Trustee thing is done - been that way since day 1.

What's new is the requirement for a user tb able to specify one trustee contact
for each deal.

My kneejerk was to go around tlkpTrustee and just embed a TrusteeContactID in
tblDeal - pointing directly to tlkpTrustee contact.

 

[Roger Carlson]

Oh, sorry. I missed the Contact part. In that case, there IS NO direct
relationship between Trustee and Deal. The relationship is between
TrusteeContact and Deal. Therefore you SHOULD put TrusteeContactID in the
tblDeal table, create a relationship and remove the relationship between
Trustee and Deal. You can get the trustee name and such from the indirect
relationship through TrusteeContact.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Pete:

The question is whether each contact relates to only one trustee or can
relate to more than one? If the former, as I'd suspect is the case, then
tblDeal needs a TrusteeContactID foreign key column as you propose and
tblTrusteeContact needs a TrusteeID foreign key column. There is no need for
a direct relationship between tblDeal and tblTrustee as tblTrusteeContact is
in fact modelling the relationship between these, the contacts entity type
also being a relationship type. So you don't need a TrusteeID foreign key in
tblDeal.

If by any chance a contact can relate to more than one trustee then you
would need another table to model the many-to-many relationship between
contacts and trustees. tblDeal would then include a 2-column foreign key,
TrusteeContactID and TrusteeID and you'd have an enforced relationship on
these columns with the additional table modelling the many-to-many
relationship between contacts and trustees. The two columns would be the
composite primary key of this additional table.

With the first scenario above you might want, when entering a Deal record in
a form, to select the trustee first, then the contact. This can be done by
means of an unbound combo box for the trustee and a bound combo box for the
contact (bound to the TrusteeContactID column) correlated with the unbound
Trustee combo box so it lists only contacts for the selected trustee. You'll
find a demo of this, using geographical data but the principle is the same,
at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps

With the second scenario you'd also use correlated combo boxes, but in this
case they'd both be bound.

Ken Sheridan
Stafford, England

 

[(PeteCresswell)]

Per Ken Sheridan:

The question is whether each contact relates to only one trustee or can
relate to more than one? If the former, as I'd suspect is the case, then
tblDeal needs a TrusteeContactID foreign key column as you propose and
tblTrusteeContact needs a TrusteeID foreign key column. There is no need for
a direct relationship between tblDeal and tblTrustee as tblTrusteeContact is
in fact modelling the relationship between these, the contacts entity type
also being a relationship type. So you don't need a TrusteeID foreign key in
tblDeal.

Suspicions confirmed.... my approach with both TrusteeID and TrusteeContactID in
tblDeal wasn't quite right.


Thanks Roger. Thanks Ken.

 

[Guest]

Yes, Ken, Roger, that was helpful, thank you.

[I have a somewhat similar situation]

 

[(PeteCresswell)]

Per Niniel:

Yes, Ken, Roger, that was helpful, thank you.

[I have a somewhat similar situation]

I think I'm gonna do a single combo box.

..Column(0) will be the TrusteeContactID.

..RecordSource will contain
- TrusteeContactID
- TrusteeName
- TrusteeContactName

..Column(1) will contain TrusteeName

..Column(2) will contain TrusteeContactName


When user drops the combo list, they'll just see a list of Trustees and contact
names sorted by Trustee/ContactName. They navigate to the
Trustee/ContactPerson they want and the deed is done.

From a developer's perspective, it seems to get around the programming overhead
of maintaining another "Trustee" combo box.

From a user's perspective, it's "one-stop shopping". Only one combo box to
pick from.