What is the best way to organize database table schemas (contacts-persons-companies)?

Question

MySQL

I think how best to organize the tables.
Essence: A repository of entities that can be persons or companies, some of which provide construction services, some legal. Each entity can have 0 or more phones, emails, and other means of communication. Number of about 500,000 entries.
It is necessary to organize the output of persons, companies, search, in all fields, checking and combining duplicates by mail / phone, etc. search by phone / email for the withdrawal of both companies and persons, by company the ability to see the persons associated with it. Synchronization with another database - if this phone / email has a connection, etc. is_builder = 0, and they have is_builder = 1, then during synchronization we change it to is_builder = 1, I don’t know where this field is better for a person or storage
Plans for organizing tables
Tablecontacts

• id
  
• type - enum type company/person
  
• person has its own unique fields - full_name, last_name,...passport...birthday......
  
• company has its own unique fields - company_name, OPF, inn, kpp, legal address
  
• statuses for both - is_builder/is_lawer
  
• created_at
  
• updated_at
  

store_contacts table - we store emails, phones, urls, skypes, etc. in one place, separating by types

• id
  
• contact_id
  
• type - enum type of emails, phones, urls, skypes, etc.
  
• value
  
• is_main
  

Now what problems do I see
1. Two tables, on the one hand, a faster search, on the other, a lot of superfluous, it is probably easier to divide into two tables: persons and companies each have their own unique fields, but in store_contacts, then what to do?
Replace one contact_id column with 2 columns: person_id and company_id, you get a bit of garbage an extra column for each row. Plus, only when separated, as I understand it, I can get a person's connection with the company that is needed.
2. What English column names are usually used for details of Russian companies?
3. Phones and emails also need features by phone type: mobile, work, etc., by email statuses - confirmed, not confirmed, permission for different types of subscriptions, etc.
How is it better to be here so as not to lose in processing speed - display specifics in separate tables and make connections with store_contacts?
4. What to do with phone numbers now in one field phone - 79991112233, is it worth adding a second column with a formatted format to speed up, for example phone_formated - [phone], i.е. give up the size of the database, to reduce the cost of representation, when displaying 100-1000 phones?
5. What is the best thing to do with duplicate Yandex emails? @ ya.ru yandex .ru
In all forms, set the autocorrect condition.

Answer 1

We close the issue with duplicate Yandex emails - we stop at checking and bringing it to ya.ru while saving it to the database.
With the rest, especially with the separation of entities, it remains unclear.
Including: It is
necessary to organize the output of persons, companies, search, in all fields, checking and combining duplicates by mail / phone, etc. search by phone / email for the withdrawal of both companies and persons, by company the ability to see the persons associated with it. companies
Synchronization with another database - if this phone / email has a connection, etc. is_builder = 0, and they have is_builder = 1, then during synchronization we change it to is_builder = 1, I don’t know where this field is better for a person or storage.
So, in short, how is it better to design a database
Table - contact
Tables - contact_person and contact_company
Tables - contact_phone, contact_email and contact_social
I correctly understood that in order to perform these tasks, make a bunch where the main contact is, and from it go for example contact_person and contact_phone
--- Added on 01/24/2018 ---
In general I decided to do this for myself, I can share it with someone who can help
Table - contact for a bunch
In it is a type (person / company) and its ID
C on the other side is a type (phone / email / social) and its ID

Answer 2

1. It is customary to name entities in the singular.
Contact has only shared fields. Additional in contact_person and contact_company (PK is FK).
2. Transliterated: inn, kpp, okpo, opf
3. Mobile/work - different store_contact.type
4. Phones all converted to a single E.164 format before insertion into the database
5. Email all converted to a single format (in lowercase and, possibly with the replacement ya.ru) before inserting into the database

Answer 3

Share everything if the project grows and changes.
the essence of a person and a company will acquire specific fields.
contacts of a person and a company will acquire specific fields.
Plus get a big waste of time to code this versatility