Database design help for Photography website

[kid320]

**I am sorry if I am posting this in the wrong forum, please feel free to move it if I am out of the proper area.**

 

Hey everyone,

 

I am attempting to design a mySQL database for a photography website.  The website is eventually going to use php to read information from the database and display it, but for now, I just need to figure a few things out on how to design it logically.

 

Here are the goals of the database:

 

-Store information on multiple picture galleries with a gallery title, path to gallery thumbnail jpg (from one of the pictures contained in the gallery) and gallery description of what kinds of pictures the gallery contains.

 

-Store information on individual pictures with a picture title, picture description, path to thumbnail jpg, path to enlarged jpg and a reference to a gallery the picture should be shown in.

 

-Store pricing information for each picture.

 

I was able to basically cover the first two points with the following tables:

=================================

GALLERY (This table will store information on the galleries that will contain the pictures).  It will have the following keys:

GalleryID (Primary Key) - An ID to easily reference this gallery from another table.

GalleryTitle - A title of the gallery

GalleryDescription - A brief descripton of what the gallery contains

GalleryThumbnailID - A reference to the primary key of the PICTURES table.  This will enable me to get a thumbnail path of an image that is included in that particular gallery.

 

For example:

 

 

GalleryID

GalleryTitle

GalleryDescription

GalleryThumbnailID

 

 

1

Philadelphia Skyline Pictures

These are pictures of the Philadelphia Skyline from various points around the city.

2

 

 

2

Sporting Events

These are pictures of various Philadelphia sporting events.

3

 

 

3

Philadelphia's Historic Buildings

These are pictures of some historic buildings in and around the Philadelphia Area.

5

 

 

=================================

PICTURES (This table will store information on each picture that will be displayed on the website).  It will have the following keys:

PictureID (Primary Key) - An ID to easily reference this picture from another table.

PictureTitle - A title of the picture

PictureDescription - A brief Description of the picture.

PictureImagePath - A filename of the picture

GalleryID - A reference to the primary key of the GALLERY table.  This will enable me to sort the pictures into indivudual galleries.

 

For example:

PictureID

PictureTitle

PictureDescription

PictureImagePath

GalleryID

1

Philadelphia Skyline - Tall

This picture of the Philadelphia Skyline was taken from the South Street Bridge.

img1994.jpg

1

2

Philadelphia Skyline - Wide

This picture of the Philadelphia Skyline was taken from the South Street Bridge.

img2948.jpg

1

3

Phillies Dugout

This is a picture of the Phillies dugout taken during a Phillies game.

img4895.jpg

2

4

Skyline from Spring Garden Street

This picture of the Philadelphia Skyline was taken from the Spring Garden Street Bridge.

img3988.jpg

1

5

City Hall - Tall

This picture of Philadelphia's City Hall was taken from the steps of the Art Museum.

img4858.jpg

3

6

Phantoms Goalie

This is a picture of a Philadelphia Phantoms goalie making a fantastic save during a game against the Hershey Bears.

img0059.jpg

2

=================================

 

Here is the part that I am having trouble with.

 

-Is there a way to put a picture into multiple galleries?  I might have a picture of a sports stadium that I might want to classify into both the sporting events category as well as the historic buildings category.

 

-I need to add pricing information for the pictures.  The problem is, I am not going to have an across the board price for every picture, some will be more expensive than others.  I am also going to offer each picture in multiple sizes with multiple matting options.    For example:

 

Some picture's will be offered as:

10x16 (print only)

10x16 (matted to 16x20)

10x16 (matted to 20x24)

5x8 (print only)

5x8 (matted to 8x11)

 

while others will be offered as:

10x10 (print only)

10x10 (matted to 20x20)

10x10 (matted to 16x20)

5x5 (print only)

5x5 (matted to 10x10)

 

For the most part, the pictures are going to be offered with one of those options or the other.  So, I want the ability to "group" those options in order to reduce redundancy in my database.  Yet, I don't want to be restricted to this, as I want the ability to add different sizes and mattings for other images (panoramic photos, for instance).

 

Also, some prints might cost $50, and others might cost $75.  No big deal, I can create a column in the PRINTS table called PriceLevel that references a PRICE table with tier 1 and 2 pricing information.  If something is assigned tier 1, it will be priced at $50, tier 2 will be priced at $75.  I can also add tiers to this table as time goes on.  If I were selling prints only, that would work perfectly.  The problem occurs when I add in the option for different matting sizes.  I need to figure out a way for whatever is storing the prints/matting sizes to work together with the PRICE table to only display sizes/prices offered with that print.  If a print is being sold as 10x16, I can't have 10x10 options coming up.  On that same note, I don't want the option to appear for a 10x10 print to be put into a 5x5 mat.  That's kind of impossible.  I have a feeling that accomplishing this is going to take 5 or 6 additional tables, which I am fine with.  As long as things are dynamic enough to allow me to add additional prices, print sizes and mattings in the future (and not really be restricted to how many I can have).

 

I am really trying to figure this one out, and have racked my brain for hours on how to accomplish this.  Am I thinking too hard?  Is this possible, or am I asking a little too much?  Any help would be graciously appreciated.

 

~Jeff

[F1Fan]

I know this isn't a response about the DB table setup, but I think it will help you. I suggest you use phpThumb (http://phpthumb.sourceforge.net/) for your pictures. You can store ONE image per picture in your gallery, and generate thumbnails on the fly for each one. It even allows you to add watermark images.

 

Then, in your DB, you can just add a table field for each of your requirements, but you won't have to worry about the thumbnail ID thing.

 

As far as the table, did you need help with the CREATE statement?

[aximbigfan]

One thing you can do, is to ID images by their MD5sums. That way, this insures no duplicates.

 

Another thing you could do is to put ALL the images somewhere, then have an index for each catigory. This is how I do it.

 

I'm actually using a custom made DB server for my purposes, so I just get the contents of an uploaded image, and stick that in the DB, then register the image in the index. When it comes time I just send out a header("Content-type: image/jpeg"); and echo the DB key that the image is in.

 

 

Fell free to ask me any other questions. Since I use a custom made DB server, I don;t know too much about MySQL. ESPECIALLY ask me if you need help creating anything to spit out a random image.

 

Chris

[kid320]

Thanks to both of you who replied.  I really appreciate it.

 

I will absolutely check out phpThumb.  That sounds like something I might be able to use.  I don't think I need help with the CREATE statement at this point, but I will let you know if I have trouble.  Thanks!

 

The suggestion to ID the images by MD5sums is a a great suggestion, and I just might implement that.  I had thought about possibly creating something to spit out a random image (perhaps for my gallery thumbnail picture), but I don't want to waste your time with this as of right now.  I will be sure to let you know if I decide to do that.  Thanks for the help!

 

~Jeff

[aximbigfan]

Your welcome!

 

The MD5 ID is how a lot of image sites do it. The main reasons are because it always makes sure the image is a unique ID, and second, it insures no duplicates, since the image with the same checksum will simply overwrite the other image.

 

Chris