Contact Mgmt db design question 1

[lmcc007]

What is the best or appropriate way of creating my contact mgmt database regarding phone numbers? Every example and template I look at all have separate fields for Business, Busines Fax, Mobile Phone, Home Phone...


I'm trying it with a table (PhoneType and PhoneNumber) and only enter the phone numbers if availalbe.


I'm wondering am I on the right track or should I do like all the examples and templates I see?


And, another reason I created a PhoneNumbers table because I would like to be able to search for phone numbers.


And, I do want my form to always display Business, Business Fax... whether there is a phone number available for the contact person or not.

 

[dhookom]

From your comments, I think you should create a related table of phone numbers and add records as needed with the PhoneType.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

Thanks Duane,

This is what I currently have:

tblPhoneNumbers
PhoneNumID
PhoneTypeID
PhoneNumber
CompanyID

I would like for my form to look like this:

Business 800-123-4567
Business 2
Business Fax 800-222-4567
Mobile 888-123-4567


I have not been able to figure out how to get it to work, so I went back over all the templates and examples of contact management programs. They each store the different phone numbers in separate fields in a table (like, Phone 1, Phone 2, Phone 3…)

 

[dhookom]

You would need a subform to add the phone numbers. If you want to display the types where there is no phone number, let's not go there unless you have a terrific reason for showing the blank records.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

None, not at all--no terrific reason. To me the books and Access training conflicts unless I am not getting something. Why are the templates set up that way--meaning, Business, mobile, home...?

 

[dhookom]

Templates are generally kept simple. This is one of those situations where either table structure might work depending on the needs of the users.

Duane
Hook'D on Access
MS Access MVP

 

[lmcc007]

ok, thanks!

 

[GKChesterton]

Duane is right about templates. But I think it's also fair to commend you for using a data-centric approach ... and bemoan the short-cuts taken in cookie-cutter apps.

Those short-cuts are becoming less tenable as communication modes/methods proliferate. Twenty years ago we had to add fax. Now the array of new tools is bewildering. ("Oh, that cell phone! I use it only for texting!")

But I'm really butting in to give a few tips based on brutal personal experience:

(1) Will you have international phone numbers? Don't trap your phone numbers in a limited format. Intern'l numbers are a classic "gotcha." For example if you use a mask for input/display then you may also need to create a work-around.

(2) Think through field types (for storage) and formatting. Phone numbers are entered and represented ever so varied:[tt]
(212) 212-1212
2122121212
212 212 1212
212 212-1212 [/tt]
It's a challenge and the issues include (1) above and the nature of the users.

(3) You may need a "comment" field in your table. There are all kinds of idiosyncratic accessory facts.

(4) Office extension can be necessary. May call for (3) above.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[lmcc007]

Thanks for the tip!

I used a mask--that is, !999\-000\-0000;0;.

Should I store the numbers without the mask in the table and just display them with the mask in the form?

 

[GKChesterton]

Another option would be to store them in the table precisely as formatted and use some form VBA to enforce conformity.

It is much simpler if you know you won't use international numbers, and you didn't speak to that.

Another consideration is whether you are considering an auto-dial feature.

But I have sad news: I don't know the answers (including what auto-dial entails). I am preparing to re-build my sorry contacts features, and I only hope I will come out with something less publicly embarrassing. Whatever the outcome, it will be better than any cookie-cutter app I have found ... they just aren't very good in my humble opinion.

 

[lmcc007]

I don't think I will be using international numbers, but I do have a friend working in Iraq but she normally calls me or I e-mail her.

Auto-dial feature, probably I won't use this feature.


"cookie-cutter app" -- I took up three Access classes and they all teach this cookie-cutter stuff. Access 2007 Inside Out and Access 2007 Bible all cookie-cutter stuff.

 

[GKChesterton]

About cookie-cutter: hooray for reusable designs, and as Duane said earlier, sometimes simple is better than pure. It's the specific area of contact data (also identified as CRM) that has left me disappointed. It seems none of them are worthy of use in the real world. If they were, I wouldn't care if they weren't paradigms of theoretical perfection, or if they had Keebler elves dancing on the splash screen.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[GKChesterton]

Sorry to add another post, but seeing your latest on international numbers I am compelled to add something:

It looks like you can format heavily for USA numbers, and stick the occasional international number in a comment field. Also I am thinking you may as well drive your formatting into the table field. You may as well make it easy to read in any scenario.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[lmcc007]

Okay, that's what I was thinking to put the few international numbers in a comment field. At this point, I don't want to worry with them.