Re: Is this doable using Postgresql crosstab or some other function?

On Tue, Apr 10, 2012 at 4:27 AM, <mika(at)digikartta(dot)net> wrote:
>
> Let's say I have tens or hundreds or thousands of feature (instance) types.
> Each of them would need its own table.
> Let's say I give for the application administrator, a possibility to create
> new feature types? He/she merely knows what's the database is.
> So I need a model which is capable for offering "dynamic table structures".
>
> I already have implemented parts which allow creating these types, creating
> instances of them and populating those instances with data. Now I have to
> come up with a method for flattening that data into one view so that it can
> be used directly.

If you want a schemaless design in a relational database, you have a
some options: EAV model, hstore, xml (soon json too) as data. I
consider these to be niche solutions not well suited to general
purpose data management. For the most part, SQL really only works
properly with a well defined schema.

Your incoming data looks to be EAV-ish. You can build horizontal
structures with crosstab and what you're trying to do looks semi
doable, but it's going to to be quite difficult.

merlin