Unifying Fields in New Table

Sorry about the vagueness of the question title -- I just don't what the best technical term is.

I have a Contact table that includes both personal and professional contact information in individually separate fields (columns): name, addresses, email addresses, etc.

However, most of the contacts do not have both sets of data. Thus, I have contacts for whom I have a complete professional address but only a personal email; or, the other way around, a person with a personal address, email and phone number but without the professional contact information.

The prevalent data, however, is the professional one.

I need to create only one contact information (1 full address, 1 phone, 1 email, etc.) in a separate table or query, which would be "populated" with either the professional or the personal fields, depending on what the original Contact table has available in it. In other words, the query(?) would look at each contact, look up whether the professional or the personal contact information is available (not blank) and then populate or create a new table with it.

The resulting table/data does not have to match; thus, it's OK to have a professional full address with a personal email address or phone number, and vice versa. The idea is to have only one set of contact information for each of the contacts, irrespective of where the data comes from.

Think about this in terms of the entities that you need to manage. If you want to have addresses, phone numbers, and E-mail addresses that you can attach to a contact independently, then you should have separate entities for each type of data. Each separate type of entity gets stored in a separate table. So you have a "Physical Address" table, a "Phone Number" table, and an "E-mail Address" table.

Now, suppose that each of these tables has one field that identifies the record as "professional" or "personal." Each of these tables also has one field that contains a Foreign Key, which is a copy of the Primary Key from a contact. With this structure, you can support a one-to-many relationship between the Contacts table and each of the subsidiary tables.

With this structure, you can assemble any combination of entities for a contact: a professional address with a personal phone number and a personal E-mail address, or any permutation of the "professional" and "personal" attributes.

However, you can also construct a contact that has more than one entity of the same type. So you can have a contact that has both a professional phone number and a personal phone number.

You may not want to have multiple entities for a contact now, but I'll bet you will in the future.

Michael S. Meyers-Jouan

Help the community by fixing grammatical or spelling errors, summarizing or clarifying the solution, and adding supporting information or resources. Always respect the original author.

Think about this in terms of the entities that you need to manage. If you want to have addresses, phone numbers, and E-mail addresses that you can attach to a contact independently, then you should have separate entities for each type of data. Each separate type of entity gets stored in a separate table. So you have a "Physical Address" table, a "Phone Number" table, and an "E-mail Address" table.

Now, suppose that each of these tables has one field that identifies the record as "professional" or "personal." Each of these tables also has one field that contains a Foreign Key, which is a copy of the Primary Key from a contact. With this structure, you can support a one-to-many relationship between the Contacts table and each of the subsidiary tables.

With this structure, you can assemble any combination of entities for a contact: a professional address with a personal phone number and a personal E-mail address, or any permutation of the "professional" and "personal" attributes.

However, you can also construct a contact that has more than one entity of the same type. So you can have a contact that has both a professional phone number and a personal phone number.

You may not want to have multiple entities for a contact now, but I'll bet you will in the future.