[thelist] Photo Gallery Database Scheme

Robert Vreeland vreeland at studioframework.com
Mon Jun 13 20:56:06 CDT 2005


I would use three tables. One to store the unique photo objects; i.e. path,
date etc.. The second table would be a category table listing the different
ways you group the photos. Each entry gets an id and a parent id. If it is a
top level item set it's parent id to itself or zero and make the primary key
a combination of the items id and parent id, this way a category can be
displayed in different contexts. The third table (contexts) would describe
where a photo object appears; i.e. category id , photo id. This way the same
photo can show up under different categories, and their is no limit to the
number of child objects you can have. Once you have defined the category to
look up, your query would be a simple join using table 1 and table 3

OK so we would have:
Table 1
photo_id
path
date_created
etc..

Table 2
Category_id
Category_display_name
Parent_ID

Table 3
Category_id (foreign key)
photo_id (foreign key)

Hope this helps


----- Original Message ----- 
From: "Michele Foster - WizarDev" <michele at wizardev.ca>
To: <thelist at lists.evolt.org>
Sent: Monday, June 13, 2005 8:03 PM
Subject: [thelist] Photo Gallery Database Scheme


> Hi List ..
>
> Since it's so quiet over here ... thought I'd bring up a question, I've
been
> mulling over a few days.
>
> I'm building a custom Photo Gallery application in ASP (using MS Access).
> Originally, my plan was to have Parent Categories and Children ... but
those
> damned kids have had kids, and now I have Grandchildren too.  There are
> currently no plans for Great-Grandchildren, but birth control isn't all
it's
> cracked up to be.   :)
>
> Ok .. so here's what I will have
>
> My Garden
>     2005
>         Waiting for Spring
>         Flowers Arrive
>         June Heat Wave
>     2004
>         Some Witty Title
>
> My Cats
>     Daniel
>     Pal
>
> etc.  you get the idea.
>
> So, my question is ... should I have separate tables for each generation?
> Or just one family table with all relationships.  I'm planning to have ID,
> Title, Path, DateCreated, WhateverElseStrikesMyFancy, in the Parent table.
> If I go the separate table route those tables will also have a field
> ChildOf, or I can add it to the Family table, so for example
>
> 1, My Garden ....
>     2, 2005, ..., ChildOf 1
>         3, Waiting for Spring, .... ChildOf 2
> etc.
>
> I can't decide which way would be best to organize this.  I'm thinking the
> one table route will result in less joins later when actually displaying
the
> photos. I would need to alias the Family table on itself to get a
> hierarchical display of all the categories, I'm not sure if that's
good/bad.
>
> All of the actual photos will be in their own table, and relate back to
the
> ID.  If I have separate tables, then I would also need to know if that
> particular photo is a Child, or Grandchild as it won't be obvious based on
> an autonumber generated ID. I'd need another field or don't use autonumber
> to generate the IDs, but some other unique identification.
>
> Each photo can only belong in one category.
>
> Thoughts?
>
> Mich
>
> __________________________
> Michele Foster
> WizarDev
> Web Design and Development Solutions
> mailto:michele at wizardev.ca
> http://wizardev.ca/
>
> -- 
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !



More information about the thelist mailing list