[thelist] Photo Gallery Database Scheme

[Michele Foster - WizarDev]

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/