Re: simple association/ordering table

On 3/24/04 11:01 PM, rollingstone wrote:

> Hi all,
> I am working on a small application to allow the user to group photos in
> an album with the functionalities to order the photos within each album
> and to associate each photo to more albums (many-to-many relationship).
>
> Here are the two tables to store the albums and the photos (I've removed
> some columns for simplicity reasons):
>
> CREATE TABLE Album (
> AlbumID INTEGER NOT NULL,
> AlbumName VARCHAR2(20) NOT NULL,
> CONSTRAINT PRIMARY KEY (AlbumID)
> );
>
> CREATE TABLE Photo (
> PhotoID INTEGER NOT NULL,
> PhotoName VARCHAR2(20) NOT NULL,
> CONSTRAINT PRIMARY KEY (PhotoID)
> );

Hi,
for some reason the second part of my post didn't come through.

Here is the association/ordering table:

CREATE TABLE Album_Photo (

	AlbumID		INTEGER NOT NULL,
	PhotoID		INTEGER NOT NULL,
	PhotoPosition	INTEGER NOT NULL,
	CONSTRAINT PRIMARY KEY (AlbumID, PhotoID),
	CONSTRAINT FOREIGN KEY (AlbumID) REFERENCES Album,
	CONSTRAINT FOREIGN KEY (PhotoID) REFERENCES Photo

Is it a good schema?
Is there anything better/simpler?

I would like to keep the application code (PHP) as simple as possible (e.g. when adding a new photo to the album, when deleting it, when reordering the photos, etc.) and that mostly depends from the db schema.

It looks like I'll have to update the PhotoPosition field for all Album_Photo records for the album if the user moves the last photo at the top of the album or if he removes the first photo of the album.

Is there any better approach to the ordering feature?

Thanks! Received on Thu Mar 25 2004 - 11:13:59 CET