suggested database schema for photos website

Question

i am trying to organize my photos and create some ajax front end. I have lots of photos and an existing hosting site that is paid for that has lots of disk space so i dont want to pay to store on picasa web or alternative.

I am creating an asp.net site to view these photos but i an trying to determine best schema to store then. A have a few fundamental questions:

Photos
Albums
Tags (maybe)

A few questions: 1. do i have a seperate table for each album or all photos in one Photos. the seperate tables would be identical so i trying to determine if there is any benefit to this seperation 2. is there any major benefit of using SQL server versus MYsql (these are my choices in this case) for this schema. I dont think i am doing anything to fancy.

Answer 1

One table for albums (id, Name, Description), one table for photos (id, name, description, PhotoData, parentAlbum), you'll get no benefit and a lot of complexity if you break up into different tables per album. If you want photos to belong to multiple albums then use a link table for the 1:N relationship Albums:Photos rather than have the album ID as part of the photo. If you go for tags decide whether you want to tag the photos or albums (or both) then maybe go for a Tags table and a 1:N mapping table for AlbumTags and PhotoTags

With regard to DB choice, the only differentiator at this level is cost so go with what you know best (if you have both available at the same price).

Answer 2

You should put all the Photos into one table, where you have 1:N with foreign key to Albums, like

Photos | id, title, url, description, album
Albums | id, title, description

where album is foreign key to Albums.id

About tags, you probably want to have many tags for one photo, so that would be N:N, where you need another table to join those two, so you have

Tags | id, name
Photo_Tag | id, id_tag, id_photo

where id_tag and id_photo are foreign keys to Tag.id respective Photo.id

If you're making the project just for personal use, I think MySQL would do just fine.