第6章 实体—属性—值

EAV/intro/count.sql
SELECT date_reported, COUNT(*) FROM Bugs GROUP BY date_reported;

• 所有的值都存在同一列中，比如Bugs.data_reported。
• 数据类型是可以比较的，因而GROUP BY可以通过比较两个值是否相等来分组。

• 实体：通常来说这就是一个指向父表的外键，父表的每条记录表示一个实体对象。
• 属性：在传统的表中，属性即每一列的名字，但在这个新的设计中，我们需要根据不同的记录来解析其标识的对象属性。
• 值：对于每个实体的每一个不同属性，都有一个对应的值。

EAV/anti/create-eav-table.sql
CREATE TABLE Issues (issue_id SERIAL PRIMARY KEY );
INSERT INTO Issues (issue_id) VALUES (1234);
CREATE TABLE IssueAttributes (
   issue_id BIGINT UNSIGNED NOT NULL,
   attr_name VARCHAR(100) NOT NULL,
   attr_value  VARCHAR(100),
   PRIMARY KEY (issue_id, attr_name),
   FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);
INSERT INTO IssueAttributes (issue_id, attr_name, attr_value)
   VALUES (1234, 'product', '1'),
          (1234, 'date_reported', '2009-06-01'), 
          (1234, 'status', 'NEW'),
          (1234, 'description', 'Saving does not work'),
          (1234, 'reported_by', 'Bill'),
          (1234, 'version_affected', '1.0'),
          (1234, 'severity', 'loss of functionality'),
          (1234, 'priority', 'high');

• 这两张表的列都很少。
• 新增的属性不会对现有的表结构造成影响，不需要新增列。
• 避免了由于空值而造成的表内容混乱。

EAV/anti/query-plain.sql
SELECT issue_id, date_reported FROM Issues;

EAV/anti/query-eav.sql
SELECT issue_id, attr_value AS "date_reported" FROM IssueAttributes WHERE attr_name = 'date_reported';

EAV/anti/insert-plain.sql
INSERT INTO Issues (date_reported) VALUES ('banana'); -- ERROR!

EAV/anti/insert-eav.sql
INSERT INTO IssueAttributes (issue_id, attr_name, attr_value)   VALUES (1234, 'date_reported', 'banana'); -- Not an error!

EAV/anti/data-types.sql
SELECT issue_id, COALESCE(attr_value_date, attr_value_datetime, attr_value_integer, attr_value_numeric, attr_value_float,   attr_value_string, attr_value_text) AS "date_reported"
  FROM IssueAttributes WHERE attr_name = 'date_reported';

EAV/anti/foreign-key-plain.sql
CREATE TABLE Issues (
   issue_id SERIAL PRIMARY KEY,   -- other columns
   status VARCHAR(20) NOT NULL DEFAULT 'NEW',
   FOREIGN KEY (status) REFERENCES BugStatus(status)
);

EAV/anti/foreign-key-eav.sql
CREATE TABLE IssueAttributes (
   issue_id BIGINT UNSIGNED NOT NULL,
   attr_name VARCHAR(100) NOT NULL,
   attr_value VARCHAR(100),
   FOREIGN KEY (attr_value) REFERENCES BugStatus(status)
);

EAV/anti/count.sql
SELECT date_reported, COUNT(*) AS bugs_per_date 
FROM (SELECT DISTINCT issue_id, attr_value AS date_reported
       FROM IssueAttributes
       WHERE attr_name IN ('date_reported', 'report_date')
     )
 GROUP BY date_reported;

EAV/anti/reconstruct.sql
SELECT i.issue_id,
       i1.attr_value AS "date_reported",
       i2.attr_value AS "status",
       i3.attr_value AS "priority",
       i4.attr_value AS "description"
 FROM Issues AS i LEFT OUTER JOIN IssueAttributes AS i1 
   ON i.issue_id = i1.issue_id AND i1.attr_name = 'date_reported'
   LEFT OUTER JOIN IssueAttributes AS i2
     ON i.issue_id = i2.issue_id AND i2.attr_name = 'status'
   LEFT OUTER JOIN IssueAttributes AS i3
     ON i.issue_id = i3.issue_id AND i3.attr_name = 'priority';
LEFT OUTER JOIN IssueAttributes AS i4 
  ON i.issue_id = i4.issue_id AND i4.attr_name = 'description';
WHERE i.issue_id = 1234;

• “数据库不需要修改元数据就可以扩展。你还可以在运行时定义新的属性。”关系数据库不支持这种程度的灵活性。当某人声称能够设计一个可以任意扩展的数据库时，他基本上用的就是EAV的设计。
• “查询时我能用的最大数量的联结是多少？”如果你需要一个支持如此多联结的查询，并且联结的数量可能会达到数据库的限制时，你的数据库的设计可能是有问题的。而EAV的设计很有可能会导致这样的问题。
• “我想象不出怎么为我们的电子商务平台生成报告。我们需要雇一个顾问来做这事。”似乎很多现有的数据库驱动的软件使用EAV的设计来支持其强大的自定义能力，而这使得很多普通的报表查询变得极度复杂甚至不切实际。

• Berkeley DB是一个流行的Key-Value存储服务，非常容易被整合进入大部分程序。 http://www.oracle.com/technology/products/berkeley-db/
• Cassandra是一个分布式的面向列的数据库，由Facebook开发，并提交给了Apache项目。http://incubator.apache.org/cassandra/
• CouchDB是一个面向文档的数据库——一个分布式的Key-Value存储系统，使用JSON编码数据。http://couchdb.apache.org/
• Hadoop和HBase组装了一个开源的DBMS，借助于Google的MapReduce算法为分布式大数据量查询提供分结构数据存储。http://hadoop.apache.org/
• MongoDB是一个像CouchDB一样的面向文档的数据库。http://www.mongodb.org/
• Redis是一个文件导向的内存数据库。http://code.google.com/p/redis
• Tokyo Cabinet是一个Key-Value存储结构，结合了POSIX DBM、GNU GDBM或Berkeley DB。http://1978th.net/

EAV/soln/create-sti-table.sql
CREATE TABLE Issues (
   issue_id SERIAL PRIMARY KEY,
   reported_by BIGINT UNSIGNED NOT NULL,
   product_id BIGINT UNSIGNED,
   priority VARCHAR(20),
   version_resolved VARCHAR(20),
   status VARCHAR(20),
   issue_type VARCHAR(10),  -- BUG or FEATURE
   severity VARCHAR(20),  -- only for bugs
   version_affected VARCHAR(20),  -- only for bugs
   sponsor VARCHAR(50),  -- only for feature requests
   FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
   FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

EAV/soln/create-concrete-tables.sql
CREATE TABLE Bugs (
   issue_id SERIAL PRIMARY KEY,
   reported_by BIGINT UNSIGNED NOT NULL,
   product_id BIGINT UNSIGNED,
   priority VARCHAR(20),
   version_resolved VARCHAR(20),
   status VARCHAR(20),
   severity VARCHAR(20), -- only for bugs
   version_affected VARCHAR(20), -- only for bugs
   FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
   FOREIGN KEY (product_id) REFERENCES Products(product_id)
);
CREATE TABLE FeatureRequests (
   issue_id SERIAL PRIMARY KEY,
   reported_by BIGINT UNSIGNED NOT NULL,
   product_id BIGINT UNSIGNED,
   priority VARCHAR(20),
   version_resolved VARCHAR(20),
   status VARCHAR(20),
   sponsor VARCHAR(50),  -- only for feature requests
   FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
   FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

EAV/soln/insert-concrete.sql
INSERT INTO FeatureRequests (issue_id, severity) VALUES ( ... ); -- ERROR!

EAV/soln/view-concrete.sql
CREATE VIEW Issues AS SELECT
	b.*, 'bug' AS issue_type
FROM
	Bugs AS b
UNION ALL
	SELECT
		f.*, 'feature' AS issue_type
	FROM
		FeatureRequests AS f;

EAV/soln/create-class-tables.sql
CREATE TABLE Issues (
	issue_id SERIAL PRIMARY KEY,
	reported_by BIGINT UNSIGNED NOT NULL,
	product_id BIGINT UNSIGNED,
	priority VARCHAR (20),
	version_resolved VARCHAR (20),
	STATUS VARCHAR (20),
	FOREIGN KEY (reported_by) REFERENCES Accounts (account_id),
	FOREIGN KEY (product_id) REFERENCES Products (product_id)
);
CREATE TABLE Bugs (
	issue_id BIGINT UNSIGNED PRIMARY KEY,
	severity VARCHAR (20),
	version_affected VARCHAR (20),
	FOREIGN KEY (issue_id) REFERENCES Issues (issue_id)
);
CREATE TABLE FeatureRequests (
	issue_id BIGINT UNSIGNED PRIMARY KEY,
	sponsor VARCHAR (50),
	FOREIGN KEY (issue_id) REFERENCES Issues (issue_id)
);

EAV/soln/select-class.sql
SELECT
	i.*, b.*, f.*
FROM
	Issues AS i
LEFT OUTER JOIN Bugs AS b USING (issue_id)
LEFT OUTER JOIN FeatureRequests AS f USING (issue_id);

EAV/soln/create-blob-tables.sql
CREATE TABLE Issues (
	issue_id SERIAL PRIMARY KEY,
	reported_by BIGINT UNSIGNED NOT NULL,
	product_id BIGINT UNSIGNED,
	priority VARCHAR (20),
	version_resolved VARCHAR (20),
	STATUS VARCHAR (20),
	issue_type VARCHAR (10),
	-- BUG or FEATURE   attributes       TEXT NOT NULL, -- all dynamic attributes
	FOR the ROW FOREIGN KEY (reported_by) REFERENCES Accounts (account_id),
	FOREIGN KEY (product_id) REFERENCES Products (product_id)
);

EAV/soln/post-process.sql
SELECT
	issue_id,
	attr_name,
	attr_value
FROM
	IssueAttributes
WHERE
	issue_id = 1234;

EAV/soln/post-process.php
<?php 
  $objects = array();
  $stmt = $pdo->query(
     "SELECT
	issue_id,
	attr_name,
	attr_value
  FROM
	IssueAttributes
  WHERE
	issue_id = 1234 )");
  while ($row = $stmt->fetch()) {
    $id = $row['issue_id'];
    $field = $row['attr_name'];
    $value = $row['attr_value'];
    if (!array_key_exists($id, $objects)) {
       $objects[$id] = new stdClass();  
    }
    $objects[$id]->$field = $value;
}

为元数据使用元数据。