News for hackers. Stuff that matters.

A comment on When to Avoid JSONB in a PostgreSQL Schema. Parent comment

Re:When to Avoid JSONB in a PostgreSQL Schema by inopinatus on Friday September 02, 2016 @10:31PM (#12417443)
No, that kind of "extension table" is a well-known antipattern being fragile, unscalable, and denormalized. Just a table with columns for id, keyname & metadata about the key will do.
[ Parent | on HN ]
Re:When to Avoid JSONB in a PostgreSQL Schema by ngrilly on Saturday September 03, 2016 @06:11AM (#12418597)
> No, that kind of "extension table" is a well-known antipattern being fragile, unscalable, and denormalized.

Ok, I agree. This is the reason why I was asking :-)

> Just a table with columns for id, keyname & metadata about the key will do.

Isn't this the Entity-Attribute-Value model (which has the well known drawback of requiring a join for each attribute)?

[ Parent | on HN ]
Re:When to Avoid JSONB in a PostgreSQL Schema by inopinatus on Sunday September 04, 2016 @01:28AM (#12422873)
Not exactly, I wouldn't be afraid of the data table having both attribute name & value columns. EAV is practically 6NF which would prohibit that. (to be honest that id field was out of habit, the known-keys table should probably be unique and indexed on the key name)
[ Parent | on HN ]
Re:When to Avoid JSONB in a PostgreSQL Schema by ngrilly on Monday September 05, 2016 @08:46AM (#12429881)
I'm sorry, but I'm still unsure what you mean. A "known-keys table" is not a common term in the literature. Can you give an example by describing the tables and their columns?
[ Parent | on HN ]