Database design - several "contact details" for different tables

Question

I have a database with tables "person", "company", "store", etc. Many of these tables must have "contact information." The ability to design this was given in the Database Project - Similar contact information for several objects. Now in my database I leave the opportunity to have several addresses, several phones and several letters for each contact data . This is my database schema:

So, I am making an intermediate table “contact” as the easiest way to associate “contact information” with each table.
My question is: is it good to do this and have a table with only one row?

Answer 1

This is how I will create your database:

address_types id unsigned int(P) description varchar(10) // Mailing, Physical, etc. addresses id unsigned int(P) line1 varchar(50) // 123 Main Street, etc. line2 varchar(50) // Default NULL city_id unsigned int(F cities.id) zip varchar(6) // 12345, A1A 1A1, etc. zip4 char(4) // Default NULL lat decimal(10,8) // 13.12345678, etc. lon decimal(11,8) // 110.12345678, etc. cities id unsigned int(P) state_id unsigned int(F states.id) name varchar(50) // Omaha, Detroit, Tampa, etc. companies id unsigned int(P) name varchar(75) // IBM, Microsoft, RedHat, etc. ... companies_addresses id unsigned int(P) company_id unsigned int(F companies.id) address_id unsigned int(F addresses.id) address_type_id unsigned int(F address_types.id) companies_contacts id unsigned int(P) company_id unsigned int(F companies.id) contact_id unsigned int(F contacts.id) contact_type_id unsigned int(F contact_types.id) companies_emails id unsigned int(P) company_id unsigned int(F companies.id) email_id unsigned int(F emails.id) email_type_id unsigned int(F email_types.id) contact_types id unsigned int(P) description varchar(10) // Home phone, Mobile phone, FAX, etc. 

In North America, phone numbers look like this: CC-AAA-EEE-SSSS-XXXXXXX, where CC is the country code, AAA is the area code, EEE is the exchange, SSSS is the station, and XXXXX is the extension.

 contacts id unsigned int(P) country_code varchar(3) area_code varchar(3) exchange varchar(3) station varchar(4) extension varchar(10) // Default NULL 

See ISO 3166-1 .

 countries id char(2) // ca, mx, us, etc. iso3 char(3) // can, mex, usa, etc. iso_num char(3) name varchar(44) // Canada, Mexico, United States, etc. email_types id unsigned int(P) description varchar(10) // Personal, Work, etc. emails id unsigned int(P) address varchar(255) // [e-mail] , etc. shops id unsigned int(P) name varchar(45) // Shop A, Shop B, etc. ... shops_addresses id unsigned int(P) shop_id unsigned int(F shops.id) address_id unsigned int(F addresses.id) address_type_id unsigned int(F address_types.id) shops_contacts id unsigned int(P) shop_id unsigned int(F shops.id) contact_id unsigned int(F contacts.id) contact_type_id unsigned int(F contact_types.id) shops_emails id unsigned int(P) shop_id unsigned int(F shops.id) email_id unsigned int(F emails.id) email_type_id unsigned int(F email_types.id) 

See ISO 3166-2 .

 states id unsigned int(P) country_id char(2)(F countries.id) code varchar(2) // AL, NF, NL, etc. name varchar(50) // Alabama, Newfoundland, Nuevo León, etc. 

Answer 2

My question is: is it good to do this and have a table with only one row?

Not really. Looking at the diagram, I have to ask: can a contact really be connected with an arbitrary number of people? If not, you should use "person" as the parent table and link to other tables.

Answer 3

Personaly, I do not like this method, because we need to create any Entity for each table and many associative tables. I suggest one table for contact information and another for addresses.

 informations - id INT - name VARCHAR - value VARCHAR - type ENUM(phone, email, url, custom) - idcompany INT NULL - idcontact INT NULL 

and

 addresses - id - address1 - address2 - district - postcode - idcity - idcompany - idcontact