规范化或非规范化：将联系方式（电话号码）存储在单独的表中？搜索性能？ [英] Normalize or Denormalize: Store Contact Details (Phone Numbers) in separate Table? Search Performance?

问题描述

我正在设计一个数据库应用程序，它可以存储简单的联系信息（First / Last Name等），我也必须存储电话号码。除了电话号码，我必须存储他们的（移动，业务等）和可能每个额外的评论。

I'm designing a database application which stores simple contact information (First/Last Name etc.) and I also have to store phone numbers. Besides phone numbers I have to store what they are for (mobile, business etc.) and possibly an additional comment for each.

我的第一个方法是规范化并保持电话号码在一个单独的表，所以我有我的'联系人'和我的'PhoneNumbers'表。 PhoneNumbers表将如下所示：

My first approach was to normalize and keep the phone numbers in a separate table, so that I have my 'Contacts' and my 'PhoneNumbers' table. The PhoneNumbers table would be like this:

Id int PK
ContactId int FK<->Contacts.Id
PhoneNumber nvarchar(22)
Description nvarchar(100)

但是，使事情变得更容易，并且如果我只是将这些信息作为每个联系人的记录的一部分存储（假设我限制可以存储的电话号码总数，总共4个数字），则保存SQL检索。

However, it would make things a lot easier AND save a SQL Join on retrieval if I just stored this information as part of each contact's record (assuming that I limit the total # of phone numbers that can be stored, to say 4 numbers total).

然而，我最终得到一个这样的丑陋结构：

However, I end up with an "ugly" structure like this:

PhoneNumber1 nvarchar(22)
Description1 nvarchar(100)
PhoneNumber2 nvarchar(22)
Description2 nvarchar(100)

等。等等。

看起来对我有业余，但这里是我看到的优点：

It looks amateurish to me but here are the advantages I see:

1）在ASP中。 NET MVC我可以简单地将输入的文本框附加到我的LINQ对象的属性，我已经完成配置记录添加和更新。

1) In ASP.NET MVC I can simply attach the input textboxes to my LINQ object's properties and I'm done with wiring up record adds and updates.

2）没有SQL连接需要检索信息。

2) No SQL Join necessary to retrieve the information.

不幸的是，我不太了解诸如表宽度问题之类的问题（我读到这会导致问题，如果它增长太大/太多的列，那性能问题出现？），那么这也意味着当我搜索一个电话号码时，如果我把它保存在一个单独的表中，我必须看4个字段而不是1。

Unfortunately I am not very knowledgeable on issues such as table width problems (I read that this can cause problems if it grows too big/too many columns and that performance issues come up?) and then also it would mean that when I search for a phone number I'd have to look at 4 fields instead of 1 if I kept it in a separate table.

我的应用程序有大约80％的搜索/数据检索活动，因此搜索效果是一个重要因素。

My application has about 80% search/data retrieval activity so search performance is an important factor.

感谢您的帮助，找到正确的方法来做到这一点。分开的桌子还是保留在一个？谢谢！

I appreciate your help in finding the right way to do this. Separate table or keep it all in one? Thank you!

推荐答案

不可能导致数据异常化的问题，但我不会建议。即使查询可能更为复杂，您可以通过多种方式操作数据更好。我建议一个这样的数据库模式：

It won't likely cause problems to have the data denormalized like that, but I wouldn't suggest it. Even though it may be more complex to query, it's better to have well formed data that you can manipulate in a multitude of ways. I would suggest a database schema like this:

Contacts:
  ID (Primary Key)
  Name
  Job Title

Phone Number Categories:
  ID (Primary key)
  Name

Phone Numbers:
  ID (Primary Key)
  Category_ID (Foreign Key -> Phone Number Categories.ID)
  Contact_ID (Foreign Key -> Contacts.ID)
  Phone Number

这允许您在允许的电话号码数量上有很大的灵活性，并且可以对它们进行分类。

This allows you a lot of flexibility in the number of phone numbers allowed, and gives you the ability to categorize them.

这篇关于规范化或非规范化：将联系方式（电话号码）存储在单独的表中？搜索性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！