Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Question Unanswered: Planning SQL & ASP Photo Album

    I'm a relative newbie at SQL, but I learn fast.

    I am planning a photo album. It is Admin only. No users will be uploading photos. I have 100% control over everything and will be inputting it myself.
    Here are the specs:

    Photos:
    They go by year and month all the way back to 1999.
    They are numbered sequentially within their respective folders. Ex:
    /Albums/2008/08/lg/200808_001.jpg
    /Albums/2008/08/lg/200808_002.jpg
    /Albums/2008/08/lg/200808_003.jpg

    The number of photos varies each month.
    Some months may be skipped.
    Each photo is related to a UserID (users may submit multiple photos each month).
    Each photo has an image title and image description.

    Users:
    There is a first name and last name.
    There is a location string.
    (Maybe a boolean if they want their location hidden.)

    Categories:
    Each month has a theme title and theme description.


    I was thinking about doing the following:
    dbo.Person
    PKPersonID
    str_FName
    str_LName
    str_Location
    bln_DisplayLoc

    dbo.Photos
    PKPhotoID
    FKPersonID
    FKThemeID
    str_ImgTitle
    str_ImgDesc

    dbo.Theme
    PKThemeID
    str_ThemeTitle
    str_ThemeDesc
    num_TotalImgs


    My questions are:
    1) Is it faster to have 20,000 images (and growing) in the dbo.Photos table or 20 Photo tables (and growing) (dbo.Photos200809, dbo.Photos200810, etc.) with 100 or so images each?

    2) Is num_TotalImgs overkill? Should I loop from 1 to num_TotalImgs or program something with EOF?

    3) Does the whole Primary and foreign key thing look okay? Do you see any room for improvement?

    Thanks in advance for any suggestions you have.

    -greek : )

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    1) One table for photos. Store the path to the images in the database, not the photo itself.

    2) Storing the number of photos is overkill, yes, as it can easily be calculated. You loop until the end of your recordset (e.g. rs.EOF).

    3) I take it only one person can "own" one photo and that one photo can only have one theme?

    P.S. drop the silly naming convention "pk...", "str_...", "num_..."
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2009
    Posts
    2
    Thanks for your help George. I will follow your recommendations.

    Thank you.

    -greek

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •