SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2010
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    First time database project advice needed

    Hi there,

    This is the first website that I built for a friend a few months ago:
    www.jpsinghphotography.co.uk

    As you'll see, it's currently all just flat HTML/CSS. I want to rebuild it as my first PHP/MySQL project, but I had a couple of questions regarding how to structure the database.

    1) I've seen a couple of tutorials on how to do this - some suggest putting the actual image files in the database using BLOB data types; others suggest putting in the file names and using these to look up the images from another folder on the server.

    What are the pros and cons of each approach? I'm guessing that once you started adding hundreds or thousands of image files to a database, things get a bit slow?

    For reference, I'd like to store images for four unique purposes - the home page slideshow, wedding portfolio shots, portrait portfolio shots, and unique galleries for clients to log-in and view online (i.e. client wedding pics)

    Which leads me to question 2)
    This Sitepoint tutorial Build a php gallery system in minutes suggests only two tables and to therefore retrieve only files of the required category to display (e.g. 'Wedding Portfolio', 'Portrait Portfolio', 'Client X' etc).
    I'd initially imagined a seperate table for each category, but I'm new to SQL! I can see how one table sorted by category could be better, but again, what are the pros and cons?

    Thanks for any and all advice

    P.S. When I tried to use the code from that tutorial, I got a lot of errors, so I'm not planning on using it at the moment!

  2. #2
    SitePoint Enthusiast
    Join Date
    Dec 2010
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, so I figure that may have been a weak question. I've had some advice on another forum to suggest that:
    1) I use filenames, not BLOBs
    2) I use seperate tables

    I want the database to allow two main things -
    Specifying which photos should go into three different portfolio galleries on the site, and,
    Displaying a unique client's photos (and only their photos!) to them when they log in.

    I've spent the afternoon considering how I should therefore design the database and I think I need three tables, like this:

    IMAGES
    id,
    filename,
    description,
    client_name,
    homepage,*
    wedding_portfolio,*
    portrait_portfolio,*
    date_uploaded,

    CLIENTS
    id,
    client_name,

    USERS
    id,
    username,
    client_name,


    *These columns will be TRUE/FALSE to declare whether the images in them should go into the respective galleries. I've come up with this as any one photo could be displayed in upto three galleries (a client, the homepage, wedding portfolio OR portrait portfolio).

    Could anyone give me feedback on this? Being so inexperienced, it's hard to draw conclusions from other peoples questions

  3. #3
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    1) I'm in the camp that says "store filenames". Why? The filesystem is a perfectly good database for storing images as it is, and extracting BLOBs from the DB will create a lot of overhead.

    2) Creating a separate table for each category is a bad idea. Why? What is you later decide to implement some kind of search function in the images? Then you'll need to search through all the tables that store a category separately. If all the images are in one table, you can search their data with just one query. I'm sure are there are many more reasons, but this is the first one that popped into my mind. Globally speaking, storing the images in separate tables won't give any performance boost over storing them in the same table anyway.

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,033
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    I would also store the images in the file system and store in the database just what is needed. Fields that you want and may need will include:

    * filename
    * extension
    * uploader
    * uploaded (date and time of when uploaded)

    Will each photo only be in one category or will they be in multiple categories. If they are in multiple categories then you'll need another table for recording each category and photo combination.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,085
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    For 1 I'm with what those guys above me said.

    In an addition to what Immerse said for 2: as a general rule of thumb it is "not done" to let a production system (i.e. a website that is done, no longer under development) automatically create new tables, or modify (ALTER) or drop existing ones. If you really do need this, it's highly likely your database schema is incorrect.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


Bookmarks

Posting Permissions

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