Please review db setup

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
speedy33417
Forum Contributor
Posts: 128
Joined: Sun Jul 23, 2006 1:14 pm

Please review db setup

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
speedy33417
Forum Contributor
Posts: 128
Joined: Sun Jul 23, 2006 1:14 pm

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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)?
User avatar
speedy33417
Forum Contributor
Posts: 128
Joined: Sun Jul 23, 2006 1:14 pm

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
speedy33417
Forum Contributor
Posts: 128
Joined: Sun Jul 23, 2006 1:14 pm

Post 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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I think you are on the right track.
User avatar
speedy33417
Forum Contributor
Posts: 128
Joined: Sun Jul 23, 2006 1:14 pm

Post by speedy33417 »

Thanks. I appreciate it.
Post Reply