giftees

[Betsy]

Hi-

I'm helping a friend develop a database. She's tracking orders for memorial
tiles that will be set in a memorial wall. So far I have just two tables.
One is tblDonors (tracking the people who pay for the tiles) and the 2nd
table is tblOrders which tracks info on the individual tile donations.
These two tables are set up in a one-to-many relationship, with tblDonors
being the parent table. Thus far it's pretty easy.

My friend anticipates that some people will buy memorial tiles as a gift for
someone else. In that case, the gift recipient's name and address will also
need to be captured. My question is about how to track that 2nd person's
infomation. Should I:

1) Add the recipient's info into the main Donor database, with an indicator
that they are the recipient of a gift and not a donor? I could link the
appropriate transaction back to them via their ID.

or

2) Add them into a separate table that is just for gift recipient info?

It seems to me that option #1 is a better choice, but I've never structured
anything this way before, and I don't want to get up to my eyeballs in this
and then find that I can't get useful reports, etc.

Has anyone got some experience to share in this kind of a design?

TIA for any/all ideas- Betsy W.

 

[Jeff Boyce]

Betsy

So, you have folks who pay, what they're paying for, and, on occasion, in
whose name (i.e. gift) they're paying...

If that third category is likely to be rare, it doesn't make practical sense
to add the fields to the tblDonor, since most of the time, these extra
fields would be empty. In that case, another table that includes DonorID,
RecipientName, and RecipientAddress would provide a place to store the rare
info.

A query can join the tblDonor and tblRecipient tables together. If you're
looking only for those Donors who are gifting, use an equi-join (where
DonorID matches in both tables). If you are looking for all Donors and any
Recipients, use a "directional" join that includes all Donors and, well, any
matching Recipient records.

If you're likely to have a lot of giftees, then, as a practical matter,
including the extra fields in the tblDonor gives you both ease of data entry
and of query, at a "cost" of a few blanks fields.

The relational db design purists will probably push the first approach.

Good luck!

Jeff Boyce
<Access MVP>

 

[Betsy]

Hi Jeff-

Thanks for this reply. I guess I wasn't clear in my original message. I
have no intention of adding more fields to my donor table, just more
records. I was thinking that since that table already serves to track
people's contact info, why should I set up another table to do the same
thing? I was thinking that I can enter the recipients into that same table,
and that I could indicate the recipient's ID number in the Order table if
the order is a gift. I think this is a "self join"...? So the Order table
would have the Donor's ID as a foreign key (one-to-many) and the Recipient's
ID as a foreign key (one-to-one)...

It is entirely conceivable that a recipient could also be a donor. In that
case I am sure that I'd want all contact info in a single table. Actually
the more I write this post the more I feel like that's the way I need to do
it. (Mentally I'm already renaming that table to tblContacts or tblPeople
in lieu of tblDonors.) However, I'll be a bit challenged by the logistics
of adding the recipient to the contact table when that table is already
opened to the record of the donor whose info I'm entering.

However, not having used a structure like this before, I'll be happy to hear
opinions to the contrary.

TIA -- again! -- Betsy W.
===================================================================

 

[Jeff Boyce]

Betsy

You've already covered the points I would have made, including renaming the
table to tblPerson or ...

The structure sounds sound, but it also sounds like you are trying to work
directly in the table ("challenged by the logistics..."). If so, build a
form. You'll have much more control over how and when data is added/edited
in the tables. If you run into questions about forms, post to the 'groups
on forms and formscoding.

Good luck

Jeff Boyce
<Access MVP>

 

[Betsy]

Hi again Jeff-

Oh gosh, I've been unclear again...! =;-) No I never work in datasheets.
But I do work in bound forms. So when I'm entering/editing data for one
donor and have a subform detailing their orders and need to indicate a gift
recipient for an order, I see a challenge if the recipient is not already in
tblPeople.

I'll play with this a bit and post over in the forms board if I stumble...

Thanks again for your replies! Betsy W.
============================================================