I'm figuring out a way to structure a new PostgreSQL database, and I'm curious if I could use a JSONB column instead of the EAV model for storing properties of an entity. This seems like a flexible way to store these kinds of things.
In the EAV case, I would have 3 tables:
materials (mat_id, mat_name, mat_descr)
material_properties (prop_id, prop_name, prop_desc)
material_property_values (mat_id, prop_id, val)
Each material can have a different set of properties.
Are there any reasons why I shouldn't just add a column mat_props JSONB
to my materials
table, and store all properties there as {"name":"value"}
? This way, querying properties as columns would be as easy as:
SELECT mat_name, mat_props->'name' as propval FROM materials
instead of joins or subselects.
Also, could I add this column in the materials
table or would it be much more performant to keep it in a separate table as:
material_properties (mat_id, properties JSONB)