Design Challenge with Customer Names

[WillYum]

Greetings,

I've hit a roadblock (and it is possible I will solve it by typing it out but otherwise I need help!!!!!).

I am designing a database for membership and customer support. The conflict I have run into is some membership types are for TWO individuals who live in the same household, thereby preventing duplicate mailings/phone calls.

However, in an effort to provide customer support we must have the ability to look up any customer/member by their own name and see their file.

I have two choices that both seem to have serious pitfalls, here is what I see:

Choice 1
tblContacts
atuContactID (Primary Key)
txtContact Full Name
txtPrimary Contact First Name
txtPrimary Contact Last Name

(( This would allow us to put "Smith Family" in Contact Full Name or "Jan Drake & Phillip Smith" in that same Full Name ))

This is the current setup, I really don't like it because you must choose Drake or Smith as the last name and if you try to look up someone who is in the database in this method, they might not display (I could require we do lookup via phone number or address but I'd rather stick with names, which are more personal in nature and important in our business).

Choice 2
tblContacts
atuContactID (Primary Key)


tblContactNames
ContactNameID (Primary Key)
atuContactID ( 1 to many link to tblContacts)
txtFirstName
txtLastName
blnPrimaryContact


While I like Choice 2 better, I see problems that I couldn't easily address. Each contact in tblContacts will also be linked to a ContactLog Table, so instead of each person having their own contact log, it would be linked to an 'account' and combined. And worse, when I go to print out a mass mailing, how will I combine both names in an appropriate manner? and what about families that prefer "The Smith Family".


Help! I'm really trying to avoid a fundemental flaw that will come back to haunt me. (Oh and I'll be really excited if there is an option 3 which I haven't considered!)

Thanks!

 

[WillYum]

Perhaps I've come up with an answer to my own question, writing it out helped a bit.

What if I'm missing tblNumberThree -- Contact Types?

Would this solution work:

tblContacts
atuContactID (Primary Key)

tblContactNames
ContactNameID (Primary Key)
atuContactID ( 1 to many link to tblContacts)
txtFirstName
txtLastName
txtFullContactsName
txtFamilyName
atuContactTypeID ( Many to 1 link to tblContactTypes)

tblContactTypes
atuContactTypeID
txtContactTypeName
txtContactTypeDesc


Eh, that doesn't seem very clean, and seems like it would make it a pain to print out just a report of our membership.

Back to the drawing board.

- William ~^~ ~^~

~ W . I . L . L . Y . U . M ~
wOrLd WiDe WhAt?

 

[WillYum]

Oh, calm down... I think I have it! And me with no delete button.

Obviously tblContacts being full of individuals is important to the expansion of the database, something I realized as I worked further.

So how does Choice 4 look?!

tblContacts
atuContactID (Primary Key)
txtFirstName
txtLastName

MembershipInBetweenTable
atuContactID
atuMemberID

tblMembership
atuMemberID (Primary Key)
txtMembershipName
txtMembershipType
dteExpiration
dteJoined
memSpecialNotes


That way, you could look up two different people who were apart of the same membership. It leaves in question how I'd count members (do you count memberships or actual members... and when you printed a list of members would it be everyone linked to that membership or just that single membership)

In either case, for mailings we'd use the "Membership Name" field. Which I could auto-fill on the Membership Entry form if it is left blank. It'll require some education in database use, that Memberships are not synonymous with Members.

If anyone sees any problem with my solution, please let me know!!! Otherwise, thanks for all your help.

- William ~^~ ~^~

~ W . I . L . L . Y . U . M ~
wOrLd WiDe WhAt?