通讯录数据库架构设计

Question

通讯录数据库架构设计

schemamicroformatsvcf-vcardnormalizinghcard

3

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

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

如果有影响的话，我正在使用 SQL Server 2005。

- palmsey

7个回答

3

不要害怕对数据进行规范化。像John提到的那样，规范化是解决问题的方法而不是问题本身。如果你试图对数据进行反规范化，只是为了避免一些连接操作，那么在未来你将会给自己带来严重的麻烦。当你拥有一个合理大小的数据集后，尝试重构这种数据将是非常困难的。

我强烈建议您查看36 Signals的Highrise。当我正在寻找在线联系人管理器时，最近有人向我推荐了它。它做得非常好。实际上，到目前为止，我唯一的反对意见是，我认为收费版本太贵了。

就目前而言，我的地址资料无法适应平面地址配置文件。我有4-5个经常使用的电子邮件地址、5个电话号码、3个地址、几个网站和IM档案，所有这些都将包含在我的联系人资料中。如果你现在开始构建联系人管理系统，并且没有架构限制（想想gmail联系人与单个电子邮件地址关联），那么请为你的用户考虑，使你的联系人结构尽可能灵活（规范化）。

干杯，-D。

- dshaw

3

为什么完全规范化会“变成一团糟”？这正是规范化可以减少混乱的原因。

- John

1

我知道SQLite，但那并没有真正帮助 - 我说的是找出最佳模式（无论数据库）来存储这些数据。

- palmsey

1

根据John的说法，我不认为使用经典的规范化模式会有什么问题。你没有提供太多信息，但是你说用户和地址之间存在一对多的关系，所以我会选择一个标准的解决方案，在地址关系中使用一个外键指向用户。

- Rob

0

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

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

CREATE TABLE IF NOT EXISTS vcards (
name_or_letter varchar(250) NOT NULL,
vcard text NOT NULL,
timestamp timestamp default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (username)
) 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，但我希望能帮到你。

- Alexx Roche

0

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

- James Inman

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Alan · Accepted Answer

考虑两个表，一个是人员信息表，另一个是他们的地址表：

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

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

People表的主键（用于唯一标识每行）是pid。AddressBook表的主键由pid和tag组成，即(pid, tag)。

下面是一些示例数据：

People

1, Kirk

2, Spock

通讯录

1, home, '123 Main Street', Iowa

1, work, 'USS Enterprise NCC-1701'

2, other, 'Mt. Selaya, Vulcan'

在这个例子中，Kirk有两个地址：一个是“家庭”地址，另一个是“工作”地址。这两个地址中的一个应该被标记为外键（类似于交叉引用），并在People表中的primaryAddressTag列中进行记录。

Spock只有一个带有“其他”标签的地址。由于这是Spock唯一的地址，pid=2的行中的primaryAddressTag列应该填入“其他”。

这个模式的好处是可以防止同一个人在不小心重复使用标签的情况下复制他们自己的任何地址，同时允许其他所有人使用任何地址标签。

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