First time database project advice needed

Databases
1

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 :slight_smile:

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

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 :confused:

3

  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. :slight_smile: Globally speaking, storing the images in separate tables won’t give any performance boost over storing them in the same table anyway.

4

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.

5

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.