数据库设计 - 多个“联系信息”为不同的表 [英] Database design - Multiple "Contact information" for different tables

问题描述

我有一个有人，公司，商店等表的数据库。这些表中的许多都必须有联系信息。设计此问题的可能性在。

  countries 
 id char（2）// ca，mx，us等
 iso3 char（3）// can，mex，usa等
 iso_num char（3）
 name varchar（44）//加拿大，墨西哥，美国等
 
 email_types 
 id unsigned int（P）
 description varchar（10）//个人，Work等
 
电子邮件
 id unsigned int（P）
地址varchar（255）// [e-mail]等
 
商店
 id unsigned int（P）
 name varchar（45）//商店A，商店B等
 ... 
 
 shops_addresses 
 id unsigned int（P）
 shop_id unsigned int（F stores.id）
 address_id unsigned int（F addresses.id）
 address_type_id unsigned int（F address_types.id）
 
 stores_contacts 
 id unsigned int（P）
 shop_id unsigned int（F stores.id）
 contact_id unsigned int（F contacts.id）
 contact_type_id unsigned int contact_types.id）
 
 stores_emails 
 id unsigned int（P）
 shop_id unsigned int（F stores.id）
 email_id unsigned int（F emails.id）
 email_type_id unsigned int（F email_types.id）
  

请参阅国际狮子会3166-2 。

 州
 id unsigned int（P）
 country_id char（2）（F countries.id）
 code varchar（2）// AL，NF，NL等
 name varchar ）// Alabama，Newfoundland，NuevoLeón等。
  

I have a database with tables "person", "company", "shop", etc. Many of these tables must have "contact information". The possibility to design this was asked in Database design - Similar Contact Information for multiple entities Now, in my database I leave a possibility to have a multiple addresses, multiple phones and multiple emails to each contact data. This is my database schema:

So, I make an intermediate table "contact" as a simplest way to link a "contact information" to each table.
My question: is it a good practice to do this and to have a table with only one row?

解决方案

This is how I would design 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.

这篇关于数据库设计 - 多个“联系信息”为不同的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！