Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SQLアンチパターン:5章EAV(エンティティ・アトリビュート・バリュー)
# 5章 EAV(エンティティ・アトリビュート・バリュー)
## intro
```
SELECT date_reported, COUNT(*)
FROM Bugs
GROUP BY date_reported;
```
## 5.2 アンチパターン:汎用的な属性テーブルを使用する
```
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');
```
### 5.2.1
```
SELECT issue_id, date_reported FROM Issues;
```
```
SELECT issue_id, attr_value AS "date_reported"
FROM IssueAttributes
WHERE attr_name = 'date_reported';
```
### 5.5.1 シングルテーブル継承
```
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)
);
```
### 5.5.2 具象テーブル継承
```
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)
);
```
### 5.5.3 クラステーブル継承
```
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)
);
```
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.