通讯录数据库架构 [英] Address book DB schema

问题描述

我需要存储用户的联系信息.我想以 hCard 的形式在页面上显示此数据，并可以以 vCard .我还希望能够通过电话号码，电子邮件等搜索数据库.

I need to store contact information for users. I want to present this data on the page as an hCard and downloadable as a vCard. I'd also like to be able to search the database by phone number, email, etc.

您认为什么是存储此数据的最佳方法?由于用户可能有多个地址，所以完整的规范化将是一团糟.我正在考虑使用XML，但对查询XML db字段并不熟悉.我仍然可以通过联系信息搜索用户吗?

What do you think is the best way to store this data? Since users could have multiple addresses, etc complete normalization would be a mess. I'm thinking about using XML, but I'm not familiar with querying XML db fields. Would I still be able to search for users by contact info?

如果要这样做，我正在使用SQL Server 2005.

I'm using SQL Server 2005, if that matters.

推荐答案

为人及其地址考虑两个表:

Consider two tables for People and their addresses:

People (pid, prefix, firstName, lastName, suffix, DOB, ... primaryAddressTag )

AddressBook (pid, tag, address1, address2, city, stateProv, postalCode, ... )

People的主键(唯一标识每一行)为pid. AddressBook的PK由pid和标记(pid, tag)组成.

The Primary Key (that uniquely identifies each and every row) of People is pid. The PK of AddressBook is the composition of pid and tag (pid, tag).

一些示例数据:

1, Kirk

2, Spock

地址簿

1, home, '123 Main Street', Iowa

1, work, 'USS Enterprise NCC-1701'

2, other, 'Mt. Selaya, Vulcan'

在此示例中，柯克有两个地址:一个家"和一个工作".可以(并且应该)在primaryAddressTag列的People中将这两个之一标记为外键(如交叉引用).

In this example, Kirk has two addresses: one 'home' and one 'work'. One of those two can (and should) be noted as a foreign key (like a cross-reference) in People in the primaryAddressTag column.

Spock有一个带有标签"other"的地址.由于那是Spock的唯一地址，因此应在primaryAddressTag列中为pid = 2输入值"other".

Spock has a single address with the tag 'other'. Since that is Spock's only address, the value 'other' ought to go in the primaryAddressTag column for pid=2.

此架构具有很好的效果，可以防止同一个人通过意外重用标签来复制自己的任何地址，同时允许所有其他人使用他们喜欢的任何地址标签.

This schema has the nice effect of preventing the same person from duplicating any of their own addresses by accidentally reusing tags while at the same time allowing all other people use any address tags they like.

此外，在primaryAddressTag中使用FK引用时，数据库系统本身将增强主地址标签的有效性(通过我们的数据库极客称为参照完整性的事情)，因此您(或任何)应用程序无需担心它.

Further, with FK references in primaryAddressTag, the database system itself will enforce the validity of the primary address tag (via something we database geeks call referential integrity) so that your -- or any -- application need not worry about it.

这篇关于通讯录数据库架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！