Okay, so is it safe to say I should use loosely use these guidelines when
deciding whether to model an attribute as a dimension
(type=[inbound,outbound]) vs. bundling with a measure (total_inbound) ?
If you know the number of values for a dimension are fixed (e.g. boolean),
then creating a measure will have benefits of:
- reduced number of rows/storage
- better performance since less indexing/vacuuming
the drawbacks are:
-rigid structure, not very extensible over time (e.g. later realize I need
to also track 'internal' calls).
In my case, I'm now needing to add another measure 'encrypted=true/false',
so my table is starting to look like
month | city_id | state_id | total_calls_inbound | total_calls_outbound |
total_calls_inbound_encr | total_calls_outbound_encr |
getting a bit hairy but the alternative seems like it would start growing
too quickly in rows and more I/O for inserts.
month | city_id | state_id | encrypted | type | total_calls
2015-01 12 2 true, false, 56
2015-01 10 4 true, true, 147
2015-01 null null 201 17 218
--
View this message in context:
http://postgresql.nabble.com/Best-practices-for-aggregate-table-design-tp5868940p5868967.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.