advise in designing some tables

[davidklonski]

Hello

I am new to MySQL, and would appreciate your advise on what would be the best way to design the following small system (I am using MySQL 5.0)

The system has photo albums which have their own data (name, category, submitter, date, number of photos)
The system also has photos which have their own data (name, size, width, height, date, submitter...)
The photos should be stored once in a big repository.
Photos can be assigned to photo albums, in a many-to-many relationship:
A photo album may contain multiple photos and a single photo may appear in multiple albums.
The system has a set of categories that the albums can belong to. An album must belong to one of the defined categories.

I would appreciate your help
Thanks

[coolT]

You might want to consider reading some more before you go ahead as this is a perfect project to start learning about tables and how to make them work together.

That beign said, here is a rough sketch of what I would do (I don't think it matters which mySQL version you are using, I'm not
familiar with the 5).

Table submitter
submitID int(11) auto_increment
name
email

Table photo
photoID int auto_increment
filename varchar(255)
size
width
height
submitter int
createdate date

Table Category
categoryID int auto_increment
categoryName
createDate

Table Album
AlbumID int auto_increment
AlbumName
categoryID int
submitter int
createDate

Table photo_album_join
entryID
photoID
albumID
CreateDate

As you can see, I leave it to you to fill in extra fields and determine proper field types. I hope you also see that ie you get the categoryID in table Album by inserting the selected category (categoryID) from the category table.