数据库设计 - 多个实体的类似联系信息 [英] Database design - Similar Contact Information for multiple entities

问题描述

我意识到，这些类型的问题的答案通常是依赖，但我仍然想知道一般共识是什么。

我正在处理多个实体，如

1. 公司


2. 慈善组织


3. 审核员


4. 库存者

等等...

哪些都有联系信息，如电子邮件，电话和地址。

我正在考虑存储联系人信息的两种设计方法是

方法1）创建角色表之间联络表和公司，慈善机构，审计师和股东。

• dbo.Company - > dbo.CompanyAddress< - dbo.Address


• dbo.Company - > dbo.Company电话< - dbo.telephone



• dbo.Company - > dbo.Companyaddress< - dbo.email




• dbo.Auditor-> dbo.AuditorAddress< - dbo.Address



• dbo.Auditor-> dbo.Auditortelephone< - dbo.telephone


• dbo.Auditor-> dbo.Auditoraddress< - dbo.email

优点，在数据库中只需要一个地址，电话和电子邮件表，并且每个实体类型的所有电话号码，地址和电子邮件都存储在一个地方
缺点是它创建了很多关联表

方法2）为每个公司，慈善机构，审计员和股东创建一个单独的联系表

• dbo.Company - > dbo.CompanyContactAddress


• dbo.Company - > dbo.CompanyContacttelephone



• dbo.Company - > dbo.CompanyContactaddress




• dbo.Auditor - > dbo.AuditorContactAddress



• dbo.Auditor - > dbo.AuditorContacttelephone


• dbo.Auditor - > dbo.AuditorContactaddress

其优点是更容易实现和维护
缺点是联系人详细信息存储在数据库的多个位置。

如果任何人有任何其他想法，将不胜感激。

非常感谢

解决方案

当你说依靠。这取决于您的数据将用于OLTP，您将查看归一化的设计，以及报告系统，您希望将数据与其他数据组件的联系信息一致地退化。

在规范化数据库中，归一化水平也可以辩论。有些人会说你的第一个场景中有联系信息。我喜欢去中间路，我会在一个表格中包含所有联系信息，其中包括地址，电话和电子邮件。

 联系
 ID，地址，地址2，城市，州，邮政编码，电话，电子邮件
  

然后与单独的表创建关系

  CompanyContact 
 ID，CompanyID，ContactID 
  

这也可以集成到公司表中，只需添加一个 ContactID 您可以使用 ContactTypes 实现一个表。

/ p>

  ContactType 
 ID，ContactType 
 1，公司
 2，慈善
 3 ，审计员
 .... 
  

然后你可以在 CompanyContact 表并删除需要的关系。虽然它符合您每种类型1次联系的情况，但不会留下增长空间。

I realise that the answer to these types of questions are often "it depends" but still I wondering what the general consensus might be.

I am dealing with multiple entities such as

1. Company
2. Charity
3. Auditor
4. Stocktaker

etc etc...

Which all have contact information such as e-mail, telephone and address.

The two design methods I was thinking to store the contact info were

Method 1) create role tables between the contact tables and company, charity, auditor and stocktaker.

• dbo.Company -> dbo.CompanyAddress <- dbo.Address
• dbo.Company -> dbo.Companytelephone <- dbo.telephone

• dbo.Company -> dbo.Companyaddress <- dbo.email

• dbo.Auditor-> dbo.AuditorAddress <- dbo.Address

• dbo.Auditor-> dbo.Auditortelephone <- dbo.telephone
• dbo.Auditor-> dbo.Auditoraddress <- dbo.email

Advantages, there only needs to be one address, telephone and email table in database and all telephone numbers, addresses and emails for each entity type are stored in one place Disadvantages are it creates a lot of associative tables

Method 2) Create a separate contact table per company, charity, auditor and stocktaker

• dbo.Company -> dbo.CompanyContactAddress
• dbo.Company -> dbo.CompanyContacttelephone

• dbo.Company -> dbo.CompanyContactaddress

• dbo.Auditor -> dbo.AuditorContactAddress

• dbo.Auditor -> dbo.AuditorContacttelephone
• dbo.Auditor -> dbo.AuditorContactaddress

Advantages of this are easier to implement and maintain Disadvantages are contact details are stored in multiple locations across the database.

If anyone has any other ideas it would be much appreciated.

Many thanks

解决方案

You are correct when you say "it depends". It depends on what your data will be used for OLTP you would look at a normalized design, and a reporting system you would want the data de-normalized with the contact information inline with the other data components.

In the normalized database, the level of normalization can also be debated. Some will say to have contact information granular like you have in your first scenario. I like to go "middle of the road" I would have all contact information in one table, which included address, telephone and email.

Contact
ID, Address, Address2, City, State, Zip, Phone, Email

Then create a relationship with a separate table

CompanyContact
ID, CompanyID, ContactID

This too could be integrated into the company table, by just adding a ContactID to the Company table and avoid the separate relationship and join.

You could also implement a table with ContactTypes.

ContactType 
ID, ContactType
1, Company
2, Charity
3, Auditor
....

Then you could specify that in the CompanyContact table and remove the need for a relationship. Although it fits your scenario of 1 contact per type it does not leave room for growth.

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