How do i create a Clienttable which have support for multiple contact information details?

[ThornofSouls]

How do i create a table which supports multiple contact information details?
Such as Addresses, eMails), Phone Numbers and so on.


Way do i want a client table which can hold multiple phone numbers for a client or addresses?
Today it is very common we have 1, 2, 3 mobiles or several eMail addresses or tow apartments as an example. Then wouldn't it be logical if the database have support for this.

How would you design the table(s)?


How would you design the forms & queries?


tblClients

• ClientID
• FirstName
• LastName
• Address
• City/Location
• ZIP
• eMail
• Phone
• Mobile
• DateOfBirth

[ItsMe]

In the most extreme case, extreme being that you need to track every possible combination, you would have separate tables for addresses, phones, emails. Then, other tables would be for AddressType, PhoneType, EmailType. Then, you need to figure out the entities associated with theses addresses, phones, and emails.

I would start by designing something where Clients, Contacts, and Others are in the same table. Then, a separate table would define whether a record is a Client, Contact, or Other. When I say Other, I am talking about something you may need to distinguish, like ConfrenceRoom or AppointmentAutomation. Things like Conference Rooms, Computers, Vehicles, etc. can have addresses and emails when these things are shared and part of an appointment/reservation system.

[ThornofSouls]

I don't think i'm in an extreme, extreme case here but i have to track client's, legal guardian (Like parents or relatives or personal or assistant(I am not familiar with these titles)) and "other contact person(s)" at the top.

Correct me if i'm wrong now.
So i have to create an individual table for: Addresses, City/location(s), ZIP(s), eMails, Phone numbers & Mobile numbers.

I believe the table design and relations would look something like this.

tblAddresses ("RELATED TO ClientID")

• AddressID
• Address
• Type (Work, Personal, Other)

tblCity/Locations ("RELATED TO Addresses")

• CityID
• Cities
• Type (Work, Home, Other)

tblZIPs

• ZIPID ("RELATED TO Addresses")
• ZIP

tbleMails ("RELATED TO ClientID")

• eMailID
• eMail
• Type ("Work, Personal, Relative, Other")

tblPhone ("RELATED TO Addresses")

• PhoneID
• Phone
• Type ("Work, Personal, Relative, Other")

tblMobile ("RELATED TO ClientID")

• MobileID
• Mobile
• Type ("Work, Personal, Relative, Other")

[ItsMe]

I would not create separate tables for Street Address, City, and Postal Code. All of these will fit nicely into a single record. In other words, one person's house is not going to be located in two cities. Although, now that I have said it, someone will prove me wrong. In fact, I have come across a situation where a shopping mall was located in two counties and there was a different sales tax imposed for stores located on opposite sides of the mall. I digress.

Just consider Normalization. If it is likely one Entity will have multiple phone numbers and this is not an anomaly, create a table for phone numbers. Create a table for phone numbers in the interest of normalization. Now consider another attribute for the same Entity. If an Entity can have multiple email addresses, create a table for email addresses.

I like the direction you are going with tblMails. Also, it is interesting how you have divided tblPhone and tblMobile. Many of my examples have one table for phones (mobile and house), tblPhone. Then a column is used to distinguish the attribute, mobile vs. home. Another column will be for business, personal, etc.. Again, normalization.

Also, I usually add a Yes/No for an IsPrimarry attribute. This way, there is a distinction for a default contact method. I will use an IsPrimary for various attributes of the address and an IsMailing as an attribute of the address, itself.

[ThornofSouls]

I would not create separate tables for Street Address, City, and Postal Code. All of these will fit nicely into a single record. In other words, one person's house is not going to be located in two cities. Although, now that I have said it, someone will prove me wrong. In fact, I have come across a situation where a shopping mall was located in two counties and there was a different sales tax imposed for stores located on opposite sides of the mall. I digress.

Not to prove you wrong but this is my point of view.
I can agree this overall and it is true you won't find a person with to houses in the same city or town. But it is more likely you find a family that have separated which means the parents could live in the same city, Or on rare cases one person might own tow apartments in the same city. However it is most likely a company have several shops, premises in the same city or area. Anyway I am just trying to find possibly scenarios. Neither how likely they are, then consider from there how complex i have to make it.

The reason to way i decided to divided tblPhone & tblMobile is because:

• If a user or client, (or company) would have multiple mobile numbers and no phone numbers (to a house) it would be unnecessary to look for a related phone number. Vise versa.
• A phone number can be associated with an address. A mobile number can't, a mobile number can only be associated with a person.

With the other tables i reasoned a postal code is associated with an address and an address is associated with a city, town, country or location, (depending on how detailed we want to be). However i would see location and address as equals more or less. Because sometimes you might just know the location and sometimes the address, and that make me unsure of these tables relation.



Well this is how i thought about it.

[ItsMe]

It can get confusing and that is why it is best to write this down on a piece of paper. Create a diagram.

This page here has a link within for a tutorial on ERD.
http://www.rogersaccesslibrary.com/f..._topic238.html

After I do some rough and high level sketches on paper, I move the diagram to an electronic format. I have been pretty happy with the services provided by Lucid.
https://www.lucidchart.com/pages/tour

Consider one Entity at a time. Each Entity will have an attribute. If a person can have a phone number, the entity (person) has an attribute (phone number). Now, ask yourself, how many phone numbers can a person have? If the answer is many, perhaps you should have a tblPhoneNumbers. I would describe Mobile or House as an attribute to PhoneNumber. It could certainly work the other way where you need to have separate tables for PhoneHouse and PhoneMobile. Then the phone number is an attribute to the entity, PhoneHouse and PhoneMobile.

You just need to look at what works best for the business rules. I usually judge this by how complex my queries get. In other words, test you table design by using the Query Designer.

One thing though, I would not consider a phone being an attribute to a house or an address. I would go ahead and duplicate phone number records, erring on the side of a phone being an attribute of a person or a Business Name or a Computer or a Conference Room. In other words, associating a phone to an address will still create duplicates in some instances. What do you do if an address has multiple phones? Which phone do I choose if I want to contact a specific person?

[ssanfu]

Years ago, I had two land lines at my house.
And don't forget about a person that lives at a house (location) but only receives mail at a post office box.

[ItsMe]

...
And don't forget about a person that lives at a house (location) but only receives mail at a post office box.

I remember a while back I had an interesting conversation with a store that rhymes with Rostco. They insisted that I had to submit a valid street address. I was tempted to offer lon and lat coordinates.

[ThornofSouls]

It can indeed get confusing sometimes. Whenever i have to set up a new kind of query of them i have don before i feel slightly confused, (sometimes).

I've checked the links, it is quite a lot of useful material, sadly some files don't exists anymore, or i can't access them from my location. Lucid-Carts looks interesting, i think i will take a closer look on it tomorrow or to the weekend.
Well i have much more to through, there might be a few more new things

One thing though, I would not consider a phone being an attribute to a house or an address. I would go ahead and duplicate phone number records, erring on the side of a phone being an attribute of a person or a Business Name or a Computer or a Conference Room. In other words, associating a phone to an address will still create duplicates in some instances. What do you do if an address has multiple phones? Which phone do I choose if I want to contact a specific person?

I can sew your point now when you describe it like this. And to answer your statements with a theory.

1. What do you do if an address has multiple phones?
   
   • If an address would have multiple forms... Then (I am just speculating now), Then i would prefer to have the phone number accosted with a "type" which describes what the phone number are associated with. I don't think this table design would be effective with private individuals, but maybe in offices.
   • I have a theory, but the more I think of it. The more complex it seems to be as very specific connections would be necessary.
   
   
2. Which phone do I choose if I want to contact a specific person?
   
   • ​I have a theory, but the more I think of it. The more complex it seems to be as very specific connections would be necessary.

I am thinking on make a small test database and compare the result, i could share the test db when i have set it up.

[CJ_London]

just one thing about the tables outlined in post #3 - they are all missing a field (called a family or foreign key FK) to link back to the client.

Which is another thing to think about - can you have more than one client at the same address? If so, you can either leave as you have (with the addition of a FK) and repeat the address as required in different records, or you need to create a link table because you have a many to many relationship - one client can have many addresses and one address can have many clients.

[ThornofSouls]

just one thing about the tables outlined in post #3 - they are all missing a field (called a family or foreign key FK) to link back to the client.

Which is another thing to think about - can you have more than one client at the same address? If so, you can either leave as you have (with the addition of a FK) and repeat the address as required in different records, or you need to create a link table because you have a many to many relationship - one client can have many addresses and one address can have many clients.

That is true, i will have it in consideration.

I'm beginning with just splitting the phone numbers and mobile number from the client table. I am designing the tables like this. Thus i'm not sure how want to design the form.

tblClients

• ClientID (PK)
• FirstName
• LastName
• ADdress
• City
• ZIP
• Email

tblPhoneNumbers

• ClientID
• PhoneID (PK)
• PhoneNumbers
• Type

tblMobileNumbers

• ClientID
• MobileID (PK)
• MobileNumbers
• Type

tblEmails

• ClientID
• EmailID
• Emails
• Type

[ItsMe]

...
I'm beginning with just splitting the phone numbers and mobile number from the client table...

This is where things can get real tricky. Is your objective to track the contact information for clients, or track information for individuals, or both? This is where you need to consider the business rules. For each customer account, is there only one contact, one individual? Does a single customer account, the entity that signs the agreement, have multiple locations? Does a single customer have a Main Office and different billing addresses?

[HiTechCoach]

That is true, i will have it in consideration.

I'm beginning with just splitting the phone numbers and mobile number from the client table. I am designing the tables like this. Thus i'm not sure how want to design the form.

tblClients

• ClientID (PK)
• FirstName
• LastName
• ADdress
• City
• ZIP
• Email

tblPhoneNumbers

• ClientID
• PhoneID (PK)
• PhoneNumbers
• Type

tblMobileNumbers

• ClientID
• MobileID (PK)
• MobileNumbers
• Type

tblEmails

• ClientID
• EmailID
• Emails
• Type

Curious, why are you separating phone number by land line and move.? I always put them int he same tablke and the type is all you need.

FWIW: I put all contacts (Client, employees, supplies, etc) in a single Contacts table. I put emails and all phone numbers in a single Contacts Contact Methods table.

If the database will be used to create mass mailing for snail mail then I would have a master address table that is formatted properly form for the post office..

[ThornofSouls]

Curious, why are you separating phone number by land line and move.? I always put them int he same tablke and the type is all you need.

FWIW: I put all contacts (Client, employees, supplies, etc) in a single Contacts table. I put emails and all phone numbers in a single Contacts Contact Methods table.

If the database will be used to create mass mailing for snail mail then I would have a master address table that is formatted properly form for the post office..

It just felt right to split the phone and mobile number when both clients and users can have multiple phone, mobile numbers and addresses exetra.

In my case the users can have many clients and a client can have many guardians, more the tow occurs when separating. Each one can have multiple numbers and addresses. For me it felt more ideal to split them into different tables and connect the with just the ID field. I am curious now. depending on how much i split the the data and link them with an ID, how much of an impact could it do to the queries and forms used for receiving the new data for a new record?Could it lower or increase the performance?

[ThornofSouls]

Model 1


Don't this model say, a record with an email, phone number & mobile number can only be associated with one type and have the same type.
If the user have three email addresses two mobile numbers and one phone number. With a template like this we would end up with one or several empty cells on multiple records. Even if we just took out the email to a separate table we would still have records with empty cells associated with a type.
Then if we splitted the email, phone, mobile number & type into different tables and connected them with a junction table or directly to a related table we would decrease the amount of empty cells in the table. Correct me if am wrong but, in the books i've read and courses taken it is always said i should keep empty table tells to a minimum. (as low as possible)

For table_B i believe this method would work since an a ZIP code is associated with the address which must be located in a city/town or other location.

ID	ClientID	Email	PhoneNumber	MobileNumber	Type
1	1		0000000000	0000000000	Home
2	1	exampleAdress@work.adress	0000000000	0000000000	Work
3	1			0000000000	Work
4	1	exampleAdress@private.adress	0000000000		Private

tbl_A

• ID - (PK)
• Email
• PhoneNumber
• MobileNumber
• Type - (Home, Work, Private, ...)

tbl_B

• ID
• Address
• City/Town
• ZIP
• Type - (Home, Work, Private, ...)