Page 1 of 1

Please review db setup

Posted: Mon Sep 18, 2006 8:53 pm
by speedy33417
I'm working on a custom photo album. I spent a couple of hours designing my database for it. Please review and give me your feedback and suggestions about it.

pictures
----------------
pic_id
pic_number
pic_album
pic_path
pic_filename
pic_viewed
pic_description
pic_date


albums
-----------------
album_id
album_name
album_viewed


paths
--------------
path_id
path_guide


comments
-------------
comment_id
comment_album_id
comment_picture_id
comment_user
comment_ip
comment_date


This is my first database driven website, so please go easy on me. :lol:
A few details. Pictures will be stored in their on folder by album name. I set up a table named path for storing domain address up until the filename of the picture. Albums could have dozens or hundreds of pictures. This way $path_guide and $pic_filename will give me a full address for the pic: http://www.mysite.com/album/pic.jpg, but I only have to store http://www.mysite.com/album/ once and reuse as many times I need it.
Also, I set up a comments table which will store comments for all pictures. If there's more than one comment for one pic, then comment_album_id and comment_picture_id will be the same, but the comment_id will be unique to each comment.

My only problem is I can see this whole thing if in the main page I have all the albums displayed and selecting any album will take me to that album that is displaying all the thumbs from that album. However what if I want to have albums that could have more albums in them (albums only, not albums and pictures as well). How should I handle that?

Am I missing anything else?

Thanks.

Posted: Tue Sep 19, 2006 10:46 am
by RobertGonzalez
Is this just for you? If so, it seems to make sense, except that doesn't appear to be a way, in this schema, for a picture to be in more than one album.

Posted: Tue Sep 19, 2006 11:03 am
by speedy33417
It is for me, yes. It's a family photo album, of sort. There's only a voluntery user_id logged for comments. With IP address for possible banning.
No picture will be in more than one album. You misunderstood me. My problem is that there is more than one level of albums.
In my main album page I categorize the albums by the countries those pictuers were taken in.
Say, I have 6 albums on my main page: UK, US, France, Italy, Spain, Germany. OK, let's say I only had one trip to Germany and made 30 pics. If I select the Germany album from my main page, then it will start giving me the thumbs of those pictures right away.
However If I live in the US and I have several albums in the US album that means when I select the US album from my main page it won't bring me to the thumb displaying page, but it will have to list the sub-albums first in my US album.
Hope it makes sense.

Thanks.

Posted: Tue Sep 19, 2006 11:19 am
by RobertGonzalez
That means you are going to need to account for depth in your albums. Right now there is no way in your current setup to allow for an album of albums. Looking at your setup, you can see that a picture belongs to an album (like your pics of Germany in the germany album). But how is your setup going to be able to tell your script that an album is made up of albums (like your US example)?

Posted: Tue Sep 19, 2006 1:04 pm
by speedy33417
That is exactly where I'm stuck.

I was thinking about adding to my album table an extra album_root column. Like so:

albums
-----------------
album_id
album_name
album_root
album_viewed

If the value of root is main, then it's listed on my main album page. If the value is germany, then it's not listed in the main page, only if I'm looking at my Germany album.

I may have to add an extra column for album_status. The value of this could determine if it contains albums or pictures.

So the final structure of the album table would be:

albums
-----------------
album_id
album_name
album_root
album_status
album_viewed

Do you think this way I should be able to achive what I was going for? Or do you recommend another approach?

Thanks.

Posted: Tue Sep 19, 2006 1:09 pm
by RobertGonzalez
What if you added an album_parent field to albums. When you search initially, it can be set to pull all albums where there is no parent field value. Then, loop through all the albums that have a parent field and match those to the listed parents.

Posted: Tue Sep 19, 2006 1:13 pm
by speedy33417
That's exactly what I was thinking my album_root is your album_parent. I also added an album_status to determine if I'm dealing with more albums or the pictures themselves.

Do you see more problems?

Posted: Tue Sep 19, 2006 1:19 pm
by RobertGonzalez
I think you are on the right track.

Posted: Tue Sep 19, 2006 1:21 pm
by speedy33417
Thanks. I appreciate it.