On 2/17/08, Maxim Khitrov <mkhitrov@gmail.com> wrote:
> So the scenario is this. We have two projects starting that will deal
> heavily with mapping spatial regions. One of the reasons I'm looking
> at PostgreSQL is the PostGIS extension that may help us in dealing
> with all the geometry.
From your description, PostGIS sounds like it would work great.
> Now here's the tricky bit. We have to be able to associate an
> arbitrary number of properties with each node and edge.
[snip]
This is actually a fairly common scenario. As you point out, the
problem case is impractical to implement in PostgreSQL (though the
relational model is not to blame), and you have enumerated some of the
possible solutions. There are a few other ways to tackle the problem,
though.
While dividing the properties into subtables either by property or by
type (eg., string_properties, int_properties -- which may result in
fever tables) results in many outer joins, you could alleviate the
need to express these joins so often by creating a view that selects
from all the tables.
You can then create rules which turn insert statements on this view
into insert statements on the various subtables. There's the question
of how efficient this will be, but it simplifies the data model
conceptually in a way that does not go against PostgreSQL's grain.
I would also look at the different types of nodes required by your
application. You say that defining a single table containing columns
for all possible properties will result in too many columns. Could you
instead isolate the types of nodes you need (outdoor nodes, indoor
nodes, and so on) and create tables for those, each of which have only
the properties that apply to those nodes?
Again you would end up with a bunch of joins, but perhaps fewer than
in the one-table-per-property scenario.
Alexander.