地址簿数据库架构

Question

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

您认为存储这些数据的最佳方式是什么？ 由于用户可能有多个地址等，因此完全规范化会很混乱。 我正在考虑使用 XML，但我不熟悉查询 XML 数据库字段。 我仍然可以通过联系信息搜索用户吗？

我正在使用 SQL Server 2005，如果这很重要的话。

Answer 1

我没有脚本，但我有 mySQL 可供您使用。 在此之前，我应该提到，在 SQL 中存储 vCard 似乎有两种逻辑方法：

1. 存储整个卡片并让数据库搜索（可能）巨大的文本字符串，并在代码的另一部分甚至是处理它们客户端。 例如

   如果不存在则创建表vcards (
   name_or_letter varchar(250) NOT NULL,
   vcard 文本不为空，
   timestamp 更新 CURRENT_TIMESTAMP 时的时间戳默认为 CURRENT_TIMESTAMP，
   主键（用户名）
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

可能很容易实现（取决于您对数据的处理），但如果您有很多条目，您的搜索将会很慢。
如果这只适合你，那么这可能会起作用，（如果它有任何好处，那么它永远不会只适合你。）然后，你可以使用你喜欢的一些漂亮的模块来处理 vCard 客户端或服务器端。分享，（或其他人与您分享。）

我见证了 vCard 的发展，并且知道将会有
将来某个时间会发生一些变化，所以我使用三个表。

第一个是卡片（这主要链接回我现有的表格 - 如果您不需要它，那么您的可以是缩减版本）。
第二个是卡定义（在 vCard 术语中似乎称为配置文件）。
最后是卡片的所有实际数据。

因为我让 DBIx::Class（是的，我是其中之一）完成所有数据库工作，（三个表）似乎对我来说工作得很好，
（尽管显然您可以收紧类型以更紧密地匹配 rfc2426 ，
但在大多数情况下，每条数据只是一个文本字符串。）

我没有规范化该人的地址的原因是我已经有了一个
我的数据库中的地址表，这三个仅用于非用户联系方式。

 CREATE TABLE `vCards` (   
 `card_id` int(255) unsigned NOT NULL AUTO_INCREMENT,   
 `card_peid` int(255) DEFAULT NULL COMMENT 'link back to user table',   
 `card_acid` int(255) DEFAULT NULL COMMENT 'link back to account table',      
 `card_language` varchar(5) DEFAULT NULL COMMENT 'en en_GB',
 `card_encoding` varchar(32) DEFAULT 'UTF-8' COMMENT 'why use anything else?',
 `card_created` datetime NOT NULL,  
 `card_updated` datetime NOT NULL,
 PRIMARY KEY (`card_id`) )
 ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='These are the contact cards'

   create table vCard_profile (
    vcprofile_id int(255) unsigned auto_increment NOT NULL,
    vcprofile_version enum('rfc2426') DEFAULT "rfc2426" COMMENT "defaults to vCard 3.0",
    vcprofile_feature char(16) COMMENT "FN to CATEGORIES",
    vcprofile_type enum('text','bin') DEFAULT "text" COMMENT "if it is too large for vcd_value then user vcd_bin",
  PRIMARY KEY (`vcprofile_id`)
) COMMENT "These are the valid types of card entry";
INSERT INTO vCard_profile VALUES('','rfc2426','FN','text'),('','rfc2426','N','text'),('','rfc2426','NICKNAME','text'),('','rfc2426','PHOTO','bin'),('','rfc2426','BDAY','text'),('','rfc2426','ADR','text'),('','rfc2426','LABEL','text'),('','rfc2426','TEL','text'),('','rfc2426','EMAIL','text'),('','rfc2426','MAILER','text'),('','rfc2426','TZ','text'),('','rfc2426','GEO','text'),('','rfc2426','TITLE','text'),('','rfc2426','ROLE','text'),('','rfc2426','LOGO','bin'),('','rfc2426','AGENT','text'),('','rfc2426','ORG','text'),('','rfc2426','CATEGORIES','text'),('','rfc2426','NOTE','text'),('','rfc2426','PRODID','text'),('','rfc2426','REV','text'),('','rfc2426','SORT-STRING','text'),('','rfc2426','SOUND','bin'),('','rfc2426','UID','text'),('','rfc2426','URL','text'),('','rfc2426','VERSION','text'),('','rfc2426','CLASS','text'),('','rfc2426','KEY','bin');

create table vCard_data (
    vcd_id int(255) unsigned auto_increment NOT NULL,
    vcd_card_id int(255) NOT NULL,
    vcd_profile_id int(255) NOT NULL,
    vcd_prof_detail varchar(255) COMMENT "work,home,preferred,order for e.g. multiple email addresses",
    vcd_value varchar(255),
    vcd_bin blob COMMENT "for when varchar(255) is too small",
    PRIMARY KEY (`vcd_id`)
) COMMENT "The actual vCard data";

这不是最好的 SQL，但我希望能有所帮助。

Answer 2

如果您假设每个用户都有一个或多个地址、电话号码等，则可以有一个“用户”表、一个“地址表”（包含主键，然后包含对用户的非唯一引用），对于电话号码 - 允许多行具有相同的 UserID 外键，这将使查询“用户 X 的所有地址”变得非常简单。

Answer 3

根据约翰的说法，我不明白经典规范化模式会出现什么问题。 您没有提供太多信息，但您说用户和地址之间存在一对多关系，因此我会选择一个标准解决方案，在地址关系中具有用户的外键。

Answer 4

我知道 SQLite，但这并没有真正的帮助 - 我正在谈论找出存储此数据的最佳模式（无论数据库如何）。

Answer 5

不要害怕标准化您的数据。 标准化，就像John提到的一样，是解决方案而不是问题。 如果您只是为了避免几次连接而尝试对数据进行非规范化，那么您将来会给自己带来严重的麻烦。 在拥有合理大小的数据集后尝试重构此类数据不会很有趣。

我强烈建议您查看 36 Signals 的 Highrise。 最近我在寻找在线联系人管理器时向我推荐了它。 它做得非常正确。 事实上，到目前为止，我对该服务唯一的反对意见是我认为付费版本太贵了——仅此而已。

就目前情况而言，我不适合单一的地址配置文件。 我有 4-5 个经常使用的电子邮件地址、5 个电话号码、3 个地址、几个网站和 IM 个人资料，所有这些我都会包含在我的联系资料中。 如果您现在开始构建联系人管理系统并且不受架构限制的阻碍（认为 gmail cantacts 被锁定到单个电子邮件地址），那么请帮您的用户一个忙，并使您的联系人结构像下面一样灵活（规范化）可能的。

干杯，-D。

Answer 6

为什么完全正常化会“一团糟”？ 这正是标准化可以减少混乱的事情。

Answer 7

考虑两个 People 表及其地址：

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

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

People 的主键（唯一标识每一行）是 pid。 AddressBook的PK是pid和tag(pid, tag)组成。

一些示例数据：

People

1, Kirk

2, Spock

AddressBook

1, home, '123 Main Street', Iowa

1, work, 'USS Enterprise NCC-1701'

2, other, 'Mt. Selaya, Vulcan'

在此示例中，Kirk 有两个地址：一个是“家庭”，一个是“工作”。 这两者之一可以（并且应该）在 PrimaryAddressTag 列的 People 中被标记为外键（如交叉引用）。

Spock 有一个带有“其他”标签的地址。 由于这是 Spock 的唯一地址，因此 pid=2 的值“other”应该出现在 primaryAddressTag 列中。

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

此外，通过primaryAddressTag中的FK引用，数据库系统本身将强制主地址标签的有效性（通过我们数据库极客称为引用完整性的东西），以便您的或任何应用程序不需要担心它。