How to store several letters, personal, etc. For a single contact in MySQL

Question

I need to store multiple email addresses for each user in MySQL and normalize it. Letters can be for work, personal, etc. Even several work letters or any other can be associated with one contact.

My idea is to have 3 tables, one for storing types of emails, another for storing contact information addresses, etc., and a third for storing email associations and contacts called contact addresses that have pk from the table email_types and contacts

A simple diagram can be seen here https://bubbl.us/?h=a7f3b/13e72f/68J/1dgjeYVbs Is this generally effective? Thus, a single contact can have any number of email addresses stored and stored outside the contact table.

thanks

Answer 1

Yes, your design is fine. It is a regular, normalized email address data model.

Answer 2

This is how I do it. I think your design is fine.

Answer 3

yes, you can also use a composite key (using a primary or unique index) in the contact_emails table using contact_id and email_type_id. Thus, one contact will have only one email of a certain type. without an index, the user can save 1+ letters from the same type of email.