我有一个包含表“人”,“公司”,“商店”等的数据库.这些表中的许多表必须具有“联系信息”.在
Database design – Similar Contact Information for multiple entities中提出了设计这种可能性的可能性
现在,在我的数据库中,我可以为每个联系人数据提供多个地址,多个电话和多个电子邮件.这是我的数据库架构：
现在,在我的数据库中,我可以为每个联系人数据提供多个地址,多个电话和多个电子邮件.这是我的数据库架构：
因此,我将中间表“联系”作为将“联系信息”链接到每个表的最简单方法.
我的问题：做这个并且只有一行的表是一个好习惯吗？
解决方法
这就是我设计数据库的方式：
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.
在北美,电话号码如下所示：CC-AAA-EEE-SSSS-XXXXXXX,其中CC是国家代码,AAA是区号,EEE是交换机,SSSS是站,XXXXX是分机.
contacts
id unsigned int(P)
country_code varchar(3)
area_code varchar(3)
exchange varchar(3)
station varchar(4)
extension varchar(10) // Default NULL
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) // support@ibm.com,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)
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.
@H_403_6@
@H_403_6@ 原文链接：https://www.f2er.com/mssql/77872.html