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.
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.