Contact Table

[tara99]

I want to define a table called Contacts. I have two types of contacts, business hour contact and after hours contact.
I need suggestion on the following that which is efficient. Or if there are different ways let me know
1.
ContactID, BHContact, BH Mobile, BHPhone, BHEmail, AHContact, AHMobile, AHPhone, AHEmail.

2.
ContactID, Contact, Mobile, Phone, EMail, Type


Thanks

[tara99]

I want to define a table called Contacts. I have two types of contacts, business hour contact and after hours contact.
I need suggestion on the following that which is efficient. Or if there are different ways let me know
1.
ContactID, BHContact, BH Mobile, BHPhone, BHEmail, AHContact, AHMobile, AHPhone, AHEmail.

2.
ContactID, Contact, Mobile, Phone, EMail, Type


Thanks

Any sugestion is welcomed

[Killer42]

Any sugestion is welcomed

Be careful what you wish for... :)

But seriously, I imagine the more usual approach would be to have a table of contacts with a Type field, and then pull together on a form (or in a query) the ones which relate to the same person.

Now that I think about it, aren't there samples of contact tables in the distributed files? Or the new table wizard, or the help text? I forget where it was.

[tara99]

Be careful what you wish for... :)

But seriously, I imagine the more usual approach would be to have a table of contacts with a Type field, and then pull together on a form (or in a query) the ones which relate to the same person.

Now that I think about it, aren't there samples of contact tables in the distributed files? Or the new table wizard, or the help text? I forget where it was.

I will check the wizard,
with the contact that I have, one person can be BHcontact, or AHcontact, and also in my case the contact doesn't have to be a person it can be a site(location).

[Killer42]

I will check the wizard,
with the contact that I have, one person can be BHcontact, or AHcontact, and also in my case the contact doesn't have to be a person it can be a site(location).

I suppose the best way to set it up is whatever works for you. I admit I'm useless when it comes to database design.

However, I was just trying to think of future flexibility - for instance, when in a year or two you come up with a third type of contact. If you have a table of contacts with a type field, and for example another table listing valid contact types and descriptions, this might be more easily expanded.

But it will be better if I step back and wait for someone with more design experience (or even just common sense) to take over. :)

[tara99]

I suppose the best way to set it up is whatever works for you. I admit I'm useless when it comes to database design.

However, I was just trying to think of future flexibility - for instance, when in a year or two you come up with a third type of contact. If you have a table of contacts with a type field, and for example another table listing valid contact types and descriptions, this might be more easily expanded.

But it will be better if I step back and wait for someone with more design experience (or even just common sense) to take over. :)

I wellcome any suggestion

[NeoPa]

I was a little unclear as I thought table 1 was a general purpose contact table. It has fields for both Business Hours and Out of Office Hours.
Assuming though, for the moment, that it is not, then Killer is absolutely right. That is sound advice generally and following it will build in extra flexibility for the future, just as Killer says.

[Killer42]

NeoPa, do you remember where that sample contact table is that I'm thinking of? It wasn't part of the NorthWind thing, was it? Wait a minute...

Ahah! Found it. If you say to create a new table in Access 2003 using the Table Wizard, the samples (at least on my system) are Mailing List, Contacts, Customers, Employees, Products, Orders, etc.

By the way tara99, but I should have read your original post more carefully - I see now that you listed two options, and one of them was exactly what I suggested. D'oh! :)

[tara99]

NeoPa, do you remember where that sample contact table is that I'm thinking of? It wasn't part of the NorthWind thing, was it? Wait a minute...

Ahah! Found it. If you say to create a new table in Access 2003 using the Table Wizard, the samples (at least on my system) are Mailing List, Contacts, Customers, Employees, Products, Orders, etc.

By the way tara99, but I should have read your original post more carefully - I see now that you listed two options, and one of them was exactly what I suggested. D'oh! :)

It it fine Killer24, i wasn't sure about it myself so I had to make sure.

thanks

[tara99]

NeoPa, do you remember where that sample contact table is that I'm thinking of? It wasn't part of the NorthWind thing, was it? Wait a minute...

Ahah! Found it. If you say to create a new table in Access 2003 using the Table Wizard, the samples (at least on my system) are Mailing List, Contacts, Customers, Employees, Products, Orders, etc.

By the way tara99, but I should have read your original post more carefully - I see now that you listed two options, and one of them was exactly what I suggested. D'oh! :)

It it fine Killer24, i wasn't sure about it myself so I had to make sure.
and the other thing that I am not sure if it make sence is that in some cases same contact name and phone number are for both type (Business Hour, After hour), so in this case what I have done is typed the information twice for both BH and AH.
Is this OK??

thanks

[tara99]

It it fine Killer24, i wasn't sure about it myself so I had to make sure.
and the other thing that I am not sure if it make sence is that in some cases same contact name and phone number are for both type (Business Hour, After hour), so in this case what I have done is typed the information twice for both BH and AH.
Is this OK??

thanks

I figured this out,
I can hve 3 contact Type (BH,AH,BH_AH) so if it is the same person for both BH and AH then I can choose type BH_AH.

thanks

[Killer42]

I figured this out,
I can hve 3 contact Type (BH,AH,BH_AH) so if it is the same person for both BH and AH then I can choose type BH_AH.
thanks

Sounds good to me. However, I see no problem with storing the details twice (storing multiple copies like this is generally referred to as redundancy). Unless you have a strong reason for one method or the other, just go with whatever seems easiest. :)

[NeoPa]

I would duplicate the data.
You will introduce logic problems processing the data if you go down the 'BH_AH' route.
Normal queries will always have to have

WHERE x='BH' OR x='BH_AH'

[MMcCarthy]

I want to define a table called Contacts. I have two types of contacts, business hour contact and after hours contact.
I need suggestion on the following that which is efficient. Or if there are different ways let me know
1.
ContactID, BHContact, BH Mobile, BHPhone, BHEmail, AHContact, AHMobile, AHPhone, AHEmail.

2.
ContactID, Contact, Mobile, Phone, EMail, Type


Thanks

This was a good question Tara

Shows you're thinking about the logic of your table structures. Keep doing this and the designing will get a lot easier.

I think you got some good suggestions here. If you still have problems post back. Otherwise I'll catch you next time.

Mary