Skip site navigation (1) Skip section navigation (2)

Re: Dynamic data model, locks and performance

From: Pierre Thibault <pierre(dot)thibault1(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Dynamic data model, locks and performance
Date: 2010-07-29 03:15:06
Message-ID: AANLkTi=Z_kmvWPx5F83Oo4XygujjpNYx2AvDsyHzNG42@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
2010/7/28 Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>

> On 29/07/10 07:06, Pierre Thibault wrote:
> I doubt anyone can make any useful recommendations without a more
> complete explanation of what you're trying to achieve and why you want
> to do what you have described.
>

Thank you Craig,

Yes, I was not very specific. I have an idea in mind. Everyone knows about
Craig's List and the hype about NoSql databases. This seems to be cool to do
full text searches in a lot of data. But what about doing more formal
searches based specific criteria like dates, numbers or other kind values on
specific fields? This where an SQL database shines.

What I would like to do is enable users to create their own data model.
Enable them to create a model and make it evolve. For example, it would be
cool to create a model to represent car adds. Then, the buyers can search
adds using the previous model with specific criteria like the color of the
car they want.

I thought about using a table where each row would represents a field in the
dynamic model. The row would contain a fields for each possible data type
supported but only one of them would be used. This would waste a lot space
and it would also be hard to query efficiently but I will not have to change
the schema as users create new models. Otherwise, I would create a standard
data model and apply the changes as users update their models.

I also like to support inheritance so we can have a simple model for regular
adds and more specfic models with more fields for car adds for example.

I have a really hard finding how to implement my idea efficiently. So the
advice of experienced database developers is what I am looking for.


>
> Re Pg-specific stuff, you should look into hstore. It might offer a way
> to avoid the need for dynamic table re-structuring.
>

I thought about using maps for the entities of my domain model. hstore seems
to be an interesting avenue.


-- 
A+

-------------
Pierre

In response to

Responses

pgsql-general by date

Next:From: rafDate: 2010-07-29 03:23:17
Subject: Re: postgres-8.4SS, pg_dump from macosx-10.6 has "ssl handshake error" 26% in
Previous:From: Craig RingerDate: 2010-07-29 02:05:27
Subject: Re: Dynamic data model, locks and performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group