Table design question

[CS]

I have a basic table: tblContacts, which stores basic, unique information
about each contact I have (FirstName, LastName, Address, Email, etc.). Each
Record in this table has a unique ContactID.

Each contact has been referred to the database by only one person (either 1.
a person already in the tblContacts and referenced by their ContactID, or 2.
a person not listed in tblContacts, who is referenced in the ContactID as "
Contact ID #UR -- which has in the name field -- Name: Unknown Referrer --
See Notes" -- and a detail is then entered in the refered Contact's "notes"
field, for future reference -- in the case of a person not currently a
"contact", I want to store this as information, but no calculations will be
performed on anyone who has referred someone else but who is not currently a
Contact).

Each contact, however, might refer many other contacts. In terms of table
structure: Is it best to separate "referrals" into another table or keep
the "referred by" in tblContacts (since that referrer can only be one
entity -- either "ContactID# . . ." or "Unknown Referrer --See notes")?

The reason I ask is this: if a single contact has referred three other
contacts, they qualify for a discount. If they are not currently listed in
the contacts list, they do not qualify for a discount and I don't care about
their info (although I might want it in future -- thus putting info into the
"notes" section). I know that I can run a query on this in the
tblContacts, but I want to set these tables up "right" at the start.

Any advice/opinions invited and welcomed.

Thanks in advance for any help,
Carol

 

[MacDermott]

Hello, Carol!

IMHO, putting information you want to reference into a "Notes" field is
tantamount to shooting yourself in the foot.
Consider this, for example - you have 3 contacts whose referral field
points to your Unknown record. In the notes, one's reference is listed as
Jim Smithe, another as J. Smith, and the third as Jimmy S. Now James Smithe
becomes an official contact; which of these 3 referrals does he get credit
for? And the same problems may exist in the address and other fields. At
the very least, when he becomes an official contact, you'll need to seek
through all the records referencing "Unknown" to find his referrals.

I'd suggest another field to identify whether a contact is "in the list" -
officially a contact. You can easily write a query which will return just
those entries which are "official", and do anything with it that you would
have done with the table as you have it designed. Now people adding a new
contact will have unofficial contacts equally available to reference, and
when an unofficial contact becomes official, all you have to do is change
that one field.

HTH

 

[CS]

Thanks so much for responding!

Would you recommend that the "referrer field" be one text field, which
references either a contactID or a name (if no ContactID available), and
have the "in the list" field as a "yes/no" field that indicates "yes" in the
contacts list, and "no" if not? And do you think that these fields belong
in the tblContacts or a separate table?

Thanks again for taking the time to respond,
Carol

 

[Guest]

There are so many ways you could go with this that it may be impossible for
somebody looking in from the outside to say whether one approach is
preferable to another. If somebody in the table (with a ContactID number)
changes their name, which name should appear in a record created before the
name change, the new one or the old one? If the new, you need to store the
ContactID. If the old, you need to store the name itself. Is ContactID a
number field? If so, you can store it as a text value, but I don't see there
is much point to that since it won't be able to be part of a relationship
with the primary key field. There may be a way around that, but it could get
messy
How about something like this? You could make a query based on the Contacts
table, and use that as the row source for a combo box. You would be able to
store the Contact ID or the name itself, depending on your needs. If the
name is not on the list, the user could click a button (or double click the
combo box, or whatever) that hides the combo box and makes a text box visible
instead. The text box, which would be not visible by default (its Visible
property set to No) could allow the user to type in a name. Also, in the
form's Current event you could hide the combo box if it is empty, and show
the text box instead. You could have the combo box and the text box in the
same physical location on the form since only one will be seen at a time.
This could be in addition to or in some combination with the idea of flagging
the field if it is an "official" contact.

 

[CS]

Brilliant Bruce, and precisely what I need. I will play around with the
text-box/combo-box switcheroo on the referrers.

The current combobox for referrers who _are_ in the Contacts list is pulled
from a query that has the ContactID field and a coctenated first/lastname
field. The user sees the first and last name in the field, but the bound
field is the ContactID.

So, now I'm wondering about this: If I did use a text field in the table
(let's call the field NonCntctRefer)to store referrers that were not in the
Contact list, would it work to pull a query of all the names in the table in
that field and build a combobox for that as well, then write a macro to show
that NonCntctRefer combo if the name was not in the ContactID combo list?

Say the macro would trigger on the "not in list" of the ContactID combo
(which is limited to list) -- it would hide the ContactID combo, show the
NonCntctRefer combo instead (not limited to list), and the user could either
select an existing name in the NonCntctRefer field, or enter a new one (I'm
thinking this would reduce possible data-entry screw-ups from mistyping, and
at least I would have a better chance of having the names in the
NonCntctRefer consistent, so that if they became a contact later, I could ID
whether they have already referred 3 people).

Let me know if I'm heading for that "shooting myself in the foot" thing
again with this approach, and thanks much for your help.
Carol

 

[Guest]

You could make a row source query for the NonContact names. It would contain
only those records that contain a value in NonCntctRefer (Is Not Null for the
criteria). Since you need to type in the name anyhow I don't see that you
would need to have anything in the Not In List event for the NonCntctRefer
combo box. You would simply enter the name when you are creating or
modifying the record. Thereafter the name would appear in the row source
query.
I would suggest that the Not In List event for the first combo box may not
be the best way to switch to the other combo box. Users will probably know
in many cases that the name is not on the Contacts list, and should be able
to double click the combo box or click a button or something to switch
immediately to the other combo box. You could have a Not In List event too,
but it may be best if there is a way other than a dummy entry to force the
other combo box to come to the surface.
Making a query to sort or filter the table according to the NonCntctRefer
field will be a simple matter, and I agree that the combo box will reduce
data entry anomalies.
Another possible approach here, since the NonCntctRefer folks may one day
become contacts, is not to have a separate field for them, but instead to add
to the table a Yes/No field for whether or not the person is a contact. The
record source for the NonCntctRefer combo box could be a query in which the
Yes/No field is No. The Not In List event could open a data entry form for
adding a name, or for making sure the user indicates whether or not the
person is a contact, or both. The same data entry form could be opened by a
command button or something, because sometimes users will know in advance
that the person is not on the list. I think you will need the data entry
form in order to separate first and last names. Also, you will need to
requery the row source after adding the name.
There are plenty of places where the "switcheroo" can be handy, but the more
I know about your database the less I think that hiding controls is the best
way to go about it.

 

[CS]

Your comments much appreciated, Bruce -- I am taking them to heart and
digging into the DB once more.

Much thanks,
Carol