From: Pierre-Frédéric Caillaud <lists@boutiquenumerique.com>
To: pgsql-sql@postgresql.org
Subject: Re: tree structure photo gallery date quiery
Date: Tue, 16 Nov 2004 19:51:31 +0100
Message-ID: <opshkmn5i0cq72hf@musicbox> (raw)
In-Reply-To: <200411161129.46131.gary.stainburn@ringways.co.uk>
References: <200411161129.46131.gary.stainburn@ringways.co.uk>
> 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lists@boutiquenumerique.com
Subject: Re: tree structure photo gallery date quiery
In-Reply-To: <opshkmn5i0cq72hf@musicbox>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox