When to use composite types and arrays and when to normalize a database?

Question

Are there any recommendations for normalizing the database or just using composite types and arrays?

When using arrays and composite types, I can only use one table. I can also normalize the database and use a couple of tables and joins.

How do you decide which option is best?

+3

rve Feb 07 '14 at 14:16

1 answer

Craig Ringer · Accepted Answer · 2014-02-07T14:40:27+0000

. , . , "" , , // . , . ( SELECT ... FOR UPDATE , ).

// , PostgreSQL , MVCC. ( TOAST , , ). , .

, , , .

IIRC , , , . Ditto arrays ( , , , - ).

(PgJDBC, psqlODBC, psycopg2 .. .. ..) , . , , .

ORM, , Hibernate, -, SQL . - , . OTOH, ORM , , - , hstore EAV , jstore ( , 9.4, json hstore).

(, , , "-" , , ).

, , , . ON INSERT OR UPDATE OR DELETE ... DO INSTEAD . .

, - "". , , . (, , " " , , SQL-), , (timestamp without time zone, tzname), .

, , . , - , . , , , "" , , .