samedi 25 juin 2016
ms-access phone table design for multiple individuals with multiple addresses
How do i design table & tblRelationship design with multiple contact information for several individuals?
Hello, i have a question about table design and table relation ship for a database who needs to keep track of multiple contact information. The contact information can of course be related with each other in this case. Especially addresses and phone numbers.
The goal is to make a structure which works for as many scenarios as possible. clients, users, students, supervisors and employees or other category's used with labels.
The contact information you want to or have to keep track of in the db for the company or organisation can differ quite a lot depending on what they work with.
This is something i have a hard time tackling, and they way to solve this is very divided over the net.
*Other sources at the bottom.*
Each client /employee /student can have one or more addresses, like privet and work addresses.
Each client /employee /student can have one or more phone numbers, like mobile, work, private (and fax)
An employee and have one or more pages as well.
Each client /employee /student can have more then one email addresses, like private and
work.
My current table design. (Related tables)
tblClients:
ClientID (PK)(Indexed no duplicates)
KlientKey (security number or personal ID)(Indexed no duplicates)
FirstName
LastName
tblAddress:
AddressID (PK)(Indexed no duplicates)
ClientID (FK)(Indexed no duplicates)
CategoryID (FK)
Address
City
ZIP
tblNumber:
NumberID (PK)(Indexed no duplicates)
ClientID (FK)(Indexed no duplicates)
Category(FK)
Number
tblEmail:
EmailID (PK)(Indexed no duplicates)
ClientID (FK)(Indexed no duplicates)
CategoryID (FK)
Email
tblCategorys:
CategoryID (PK)(Indexed no duplicates)
Category (1; Private, 2; Phone, 3; Mobile, 4; Work, 5; Fax)
This structure generates duplicates whenever the data is listed in a form, query or report. And the issue is related to phone numbers and addresses.
The conflict occurs whenever two addresses or phone numbers with the under the same category has to be displayed in a list.
Other sources with the same or similar topic:
http://www.sqlservercentral.com/Forums/Topic637723-361-1.aspx
Normalize or Denormalize: Store Contact Details (Phone Numbers) in separate Table? Search Performance?
Inscription à :
Publier les commentaires (Atom)
Aucun commentaire:
Enregistrer un commentaire