I have some data that are designed weird! Now I want to create ERD for it. But there are some problems. There are some data stored in hstore which have relationship with the primary key of other tables. To demonstrate, see the code below:
CREATE EXTENSION hstore;
CREATE TABLE books (
id serial PRIMARY KEY,
title VARCHAR (255),
attr hstore
);
INSERT INTO books (title, attr)
VALUES
(
'PostgreSQL Tutorial',
'"paperback" => "243",
"publisher_id" => 1,
"language" => "English",
"ISBN-13" => "978-1449370000",
"weight" => "11.2 ounces"'
);
INSERT INTO books (title, attr)
VALUES
(
'PostgreSQL Cheat Sheet',
'"paperback" => "5",
"publisher_id" => 2,
"language" => "English",
"ISBN-13" => "978-1449370001",
"weight" => "1 ounces"'
);
CREATE TABLE publishers (
publisher_id serial PRIMARY KEY,
name varchar (50)
);
INSERT INTO publishers (name)
VALUES ('publisher1'), ('publisher2');
There is no way to make attr->publisher_id a foreign key referencing to the publisher_id in the publishers table.
How I show their relationship in a diagram (preferably ERD)?
