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?

Aucun commentaire:

Enregistrer un commentaire