php - 此场景中正确的 mysql 表设计/关系

Question

我遇到这种情况，需要有关数据库表设计的建议。

背景

我正在用 PHP 开发一个应用程序(准确地说是 cakephp )。当我们上传 xml 文件时，它会解析该文件并将数据保存在数据库中。这些 XML 可以是文件或 URL 提要，这些是从不同的数据供应商处购买的。它旨在从源网址收集各种场馆数据，场馆可以是酒店、电影院、学校、餐馆等任何地方。

问题

这些 field 的初始表结构如下。表最初旨在存储通用信息。

id
Address
Postcode
Lat
Long
SourceURL
Source
Type
Phone
Email
Website

随着来自不同来源的数据越来越多，我意识到不同类型的场馆有很多属性。

例如酒店可以具有一些属性，例如

price_for_one_day, types_of_accommodation, Number_of_rooms etc

学校不会有它们，但有不同的属性。餐厅会有一些其他属性。

我的第一个想法是创建两个名为 vanue_attribute_names 和 Venue_attributes 的表

##table venue_attribute_names
_____________________________
id
name

##table venue_attributes
________________________
id
venue_id
venue_attribute_name_id
value

因此，如果我检测到任何新属性，我想创建一个属性，并在属性表中创建一个具有关系的值。但我怀疑这不是正确的方法。我相信还有其他方法吗？此外，如果表变得巨大，由于连接和 SQL 查询的增加，可能会出现性能问题

创建尽可能宽的表并将所有可能的属性作为列是正确的方法吗？请告诉我。如果有任何我可以引用的链接，我可以关注它。谢谢

Answer 1

这是一个令人惊讶的常见问题。

您描述的设计通常称为“实体/属性/值”或 EAV。它的优点是允许您存储各种数据，而无需提前知道该数据的架构是什么。它的缺点是难以查询 - 想象一下查找给定位置的所有酒店，每日房价在 100 美元到 150 美元之间，名称以“华尔道夫”开头。针对所有属性编写查询并应用 bool 逻辑很快就会变得比您希望的更加困难。您也无法轻松应用数据库级一致性检查，例如“hotel_name 不得为空”或“daily_room_rate 必须为数字”。

如果这些问题都不让您担心，也许您的设计有效。

第二个选项是将“公共(public)”字段存储在传统的关系结构中，但将变体数据存储在某种文档中 - MySQL supports XML ， 例如。这允许您定义 XML 模式，并使用 XPath 等进行查询。

这种方法可以为您提供比 EAV 更好的数据完整性，因为您可以应用架构约束。这确实意味着您必须为您正在处理的每种类型的数据创建一个架构。这对你来说可能没问题——我猜该公司不会每周增加几十种新的 field 类型。

XML 查询的性能可能很棘手，通用工具和开发方法将使构建比“仅 SQL”更困难。

如果您想坚持使用关系数据库，最后的选择就是硬着头皮使用“纯”SQL。您可以创建一个具有公共(public)属性的“主”表、一个具有餐厅特定属性的“餐厅”表、一个具有酒店属性的“酒店”表。只要您拥有可管理数量的 field 类型，并且它们不会不可预测地突然出现，这种方法就有效。

最后，您可以查看 NoSQL 选项。