我有一个包含表“人”,“公司”,“商店”等的数据库.这些表中的许多表必须具有“联系信息”.在
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.