Re: Dynamic data model, locks and performance

From: Andreas Joseph Krogh <andreak(at)officenet(dot)no>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Dynamic data model, locks and performance
Date: 2010-07-30 20:08:41
Message-ID: 4C533149.7020401@officenet.no
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 07/29/2010 05:15 AM, Pierre Thibault wrote:
> 2010/7/28 Craig Ringer <craig(at)postnewspapers(dot)com(dot)au
> <mailto: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.
>

You might want to take a look at TopicMaps, http://www.topicmaps.org/
One of the greatest topicmap-engines is opensource: http://www.ontopia.net/
It uses, if you want, an RDBMS (PostgreSQL is fully supported) underneath.

--
Andreas Joseph Krogh<andreak(at)officenet(dot)no>
Senior Software Developer / CTO
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Rosenholmveien 25 | know how to do a thing and to watch |
1414 Trollåsen | somebody else doing it wrong, without |
NORWAY | comment. |
| |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John R Pierce 2010-07-30 20:20:56 Re: Dynamic data model, locks and performance
Previous Message Greg Smith 2010-07-30 16:13:52 Re: Comparison of Oracle and PostgreSQL full text search