Re: DB design: How to store object properties?

On 2/17/08, Maxim Khitrov <mkhitrov(at)gmail(dot)com> wrote:
> The simplest design would be to create two tables, one for nodes
> another for edges, and create a column for every possible property.
> This, however, is huge waste of space, since there will not be a
> single node or edge that will make use of all the defined properties.
> There may be hundreds of properties, but each node may use ten on
> average. That's the question - how do you represent this information
> in the database in a space-efficient manner. We still have to be able
> to search for specific nodes given their properties. For example –
> find all the nodes located within radius z of coordinate x,y. PostGIS
> will handle the special bit, but there has to be an efficient way of
> accessing this information/property for each node that has it.

If you have a lot of columns but most of them are NULL for a given
record, the tuple size won't actually be that big. PG stores a bitmap
in each tuple telling which columns are NULL, and then only stores the
non-NULL column vales. So this approach may end up being reasonably
efficient for you.

-Doug