Help with photo album database...

I am building a photo album webpage and am not sure exactly how I
should organize the database. Here's where Im at so far...

I have one table called 'images' which contains the columns 'albumID',
'url', 'title', and 'description'. Then another table called 'albums'
which contains the column 'name'.

The part where I am having the most trouble is the 'images.albumID'
column. Some photos should be placed in mutliple albums, so should the
'albumID' column be an array type?

Also, I would like to have sub-albums within larger albums. So instead
of having a column called 'images.albumID', should I instead have an
array column called 'images.location' which declares the locations of
where the photo exists? (eg. "Nick's photo album > Vacations > Hawaii"
and "Nick's photo album > Sports > Surfing"). I guess I would also
have to add a 'location' field to the 'albums' table also so they can
be nested.

I would then access the images through JSP/PHP/ASP/etc with ("SELECT *
FROM 'images' WHERE 'location' = albums.location")

Does anyone have any experience with something like this or know of
the best way to do this??? -Nick

Nick wrote:

> I am building a photo album webpage and am not sure exactly how I
> should organize the database. Here's where Im at so far...
>
> I have one table called 'images' which contains the columns 'albumID',
> 'url', 'title', and 'description'. Then another table called 'albums'
> which contains the column 'name'.
>
> The part where I am having the most trouble is the 'images.albumID'
> column. Some photos should be placed in mutliple albums, so should the
> 'albumID' column be an array type?
>
> Also, I would like to have sub-albums within larger albums. So instead
> of having a column called 'images.albumID', should I instead have an
> array column called 'images.location' which declares the locations of
> where the photo exists? (eg. "Nick's photo album > Vacations > Hawaii"
> and "Nick's photo album > Sports > Surfing"). I guess I would also
> have to add a 'location' field to the 'albums' table also so they can
> be nested.
>
> I would then access the images through JSP/PHP/ASP/etc with ("SELECT *
> FROM 'images' WHERE 'location' = albums.location")
>
> Does anyone have any experience with something like this or know of
> the best way to do this??? -Nick

For the photo album that I created, the database structure looks like this:

CREATE TABLE gallery_index (
category int(10) unsigned NOT NULL default '0',
image int(10) unsigned NOT NULL default '0',
KEY x (category,image)
) TYPE=MyISAM;

CREATE TABLE gallery_images (
id int(11) NOT NULL default '0',
description text NOT NULL,
file varchar(50) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM;

CREATE TABLE gallery_categories (
id int(11) NOT NULL auto_increment,
name varchar(100) NOT NULL default '',
description text NOT NULL,
num_pics int(11) NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;


I have attached the class definition that I use so you can get an idea
of how this datbase would work.

I only use one category (album) for each image, but to display it in
additional categories, you'd just have to add additional rows to the
gallery_index table - no need for duplicate entries in the other tables.

I really cannot releases any more of the code than this, but hopefully
it's something to get you started with. The class uses MetaBase for
database abstraction, so the SQL may be a little difficult to follow at
first.

HTH

--
Justin Koivisto - spam@koivi.com
PHP POSTERS: Please use comp.lang.php for PHP related questions,
alt.php* groups are not recommended.