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

Re: tree structure photo gallery date quiery

From: Pierre-Frédéric Caillaud <lists(at)boutiquenumerique(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: tree structure photo gallery date quiery
Date: 2004-11-16 18:51:31
Message-ID: opshkmn5i0cq72hf@musicbox (view raw or flat)
Thread:
Lists: pgsql-sql
> I'm looking at the possibility of implementing a photo gallery for my
> web site with a tree structure, something like:

	You don't really want a tree structure, because one day you'll want to  
put the same photo in two galleries. Suppose you take a very interesting  
photo of celery during your trip to china, you might want to create a  
'Trip to China' folder, and also a 'Celery' folder for your other celery  
photos... well, if you don't like vegetables, it also works with people,  
moods, geographic regions, themes, etc. You could define this structure :

	You could then define tables describing themes, and/or keywords, link  
photos with these themes and keywords, and define a folder as either being  
a specific collection of photos, or as a collection of one or several  
themes.

	From a tree, it becomes a bit more like a graph.

	Themes can also be organized and relationed together.

	This opens the path to easy searching and cataloguing ; is not that much  
more difficult to do, and in the end you'll have a much better system.

> How would I go about creating a view to show a) the number of photos in
> a gallery and b) the timestamp of the most recent addition for a
> gallery, so that it interrogates all sub-galleries?

	If you're concerned about performance, you should do this in a  
materialized view updated with triggers. If you can afford a seq scan on  
every time, a few stored procs should do the trick.

In response to

pgsql-sql by date

Next:From: Thomas F.O'ConnellDate: 2004-11-16 19:45:45
Subject: Counting Distinct Records
Previous:From: Bruno PrévostDate: 2004-11-16 16:49:11
Subject: Re: Table definition

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