database design question for galleries
 

I'm creating a script that will read from a database and create galleries from them. Each gallery will also link to the site its advertising.

I don't want to have a limit as to how many images a gallery will have, my question is how should I make a database for this? I also want to store the name of the image and its alt tag, the biggest problem is this. Should I store them in the same gallery table as in example 1 with possibly a delimiter, or should store it in a separate table in example 2 (which will have each image from all galleries).

I hope this makes sense, I will appreciate any advice or even any new ideas. Thanks :)

Example 1

Site Table

-id
-name
-link

Gallery Table

-id
-site_id
-num of images
-meta_title
-meta_desc
-meta_keywords
-header
-description
-imagename (names separated by delimiter)
-alt tag (descriptions separated by delimiter)


Example 2

Site Table

-id
-name
-link

Gallery Table

-id
-site_id
-num of images
-meta_title
-meta_desc
-meta_keywords
-header
-description

Gallery_images Table

-id
-gallery_id
-imagename
-alt tag

imo, example 2 is better.
i would also add a few date fields - creation date, last update date.
and some extra fields for statistics, (to enable you to know which image is better than another, which one is being clicked more often...).

Hey troubled, I honestly appreciate the advice and the extra tips :) The statistics makes a lot of sense, especially for a preview thumbnail, thank you :)

Any other advice/tips would be appreciated :)

I assume you are going to use the foreign key options within the database?

I wouldnt make a field 'num of images' in the Gallery table. Just make a count on Gallery_images table. This way you will never have to manually update your Gallery table if the images set get bigger or smaller.

If you dont mind me asking, are you going to use mysql or postgresql, and why?

I'm actually in the process of building a custom gallery script at the moment like yours it currently doesn't do stats, but it's mainly coded using the different features and scripting in PHP, with a very basic (ok kinda basic) db backend which feeds most of what you see on the front, if you wanted to take a look at the system feel free, my development site is up under foxydrop.com. I would be happy to discuss some of the workings with you since nothing is overly technical. I'm running mine on mysql

Rob

Hey, thanks for the suggestions.

Sorry, I kinda always do my rough outlines a little sloppy and don't always include everything I was thinking. I'll be using the foreign get for the gallery_id in the Gallery_images. I'm just in the thinking process of this, if you have any better suggest for that, I would appreciate it.

Yes, you are right about the 'num of images' I can use the count function in mysql but I'm wondering if it might just be better in the long run to have it there. I'll try to think if they're benefits of having it.

Yeah i don't mind you asking. I'm actually just using this to get back into programming. Its been a while for me so I'm trying to do a project that I can use for my sites and as portfolio work. It will be done using PHP and mysql. The rough programming is pretty much done, just have to fancy it up now. I'll be adding a function where you can upload .zips (kind of done already), use smarty for templating and caching. I'm also using imagemagick and a movie converter, but forget the name since its been a while since I have played with it. Once its done I'll had some Ajax to it wherever it would work.

I'll be honest, this is my first time checking out PostgreSQL and I actually like the functions that it carries. Its has a lot of things I would love to use, but right now I think I'll try to finish in Mysql and then switch over once the program is actually done if I feel it will benefit me or maybe I can use as portfolio work.

Are you doing a lot of programming projects?

Hey Rob,

Yeah I checked out the site and I'm loving it. Its pretty much what I have visioned, even down to when you click an image. Do you have icq? I haven't touched mine in so long, but i'll try to re-install it and add ya. Would really like to know how you did some things :) For example if you used gd2, or imagemagik for your preview pics. Above you can get an idea of how I want to do things

I'm not gonna get to involved in stats right now, but I think they can really be beneficial if you track what images are getting clicked the most and you can use that as your thumbnail preview.

I'm also thinking of doing a traffic script down the road. I wanted something that i can use for 'babe' type sites where you exchange galleries and for just normal link exchanging processes. But I'm wondering if thats just a waste of time and If I can just buy something really good instead.

Hey,

I have not used any programs to produce my site other than a microsoft expression one to reduce all the pics and the gimp to create others, the original ones i did using the gimp then bulked them up and ran them through an auto function to reduce them by the hundreds for the portrait ones , the landscape photos i have done their thumbnail by hand. I can code the images to auto thumbnail but it increases the server load for something which would be a fixed size. (although i could get it to auto create the thumbnail on first viewing and then use that file :D)

The whole script is very light weight with not a lot to it, I have been coding for a while and scripted it all by hand, I don't have much experience within the industry but I am willing to learn and know a few quick security fixes to drop into my code, I have it all running off mysql and very small php scripts with mod_rewrite doing a lot of the front end linking and making it look pretty, I'm a fan of pretty linking so you will notice not a single external link will show it's true destination it's run through a quick redirect script which could have stats added very quickly so you could see which models links were being clicked the most.

If you are around when I get back from college I will catch you on icq, I have accepted your request.

Rob

Thats funny. Im making an TGP site with, for the first time for me, a mysql backend. I usually use postgresql.

Personally, i have abandoned the smarty template engine. As php is in essence a template language, i've switched to a very simplistic way of creating my own template.
Create a .html file and make your own template vars with {} around them.

In php use preg_replace() to replace those template vars and in the end echo the master template file. In essence, you do the same as smarty but without the overhead.

I have a few projects in the making an a few that need some desperate overhauling ;)

Awesome, thanks for the reply :) I'll try to reach you on icq sometime. I'm rarely on, so maybe I'll shoot you some emails down the road :) Thanks for sharing how you did the site.

The reason I'm even trying smarty was because some programming employers ask for it. But I agree with you and I was originally thinking of making it the way your saying. Though I'll always be keeping the original images so that I can edit them again later if needed - change thumbnail size or whatever. I also like the idea of having the .html extension. But you'll have to store pretty much everything in the database if you want to edit the file down the road, titles, keywords, headers, etc.

Hmm, maybe I'll just go back to my original idea and not use smarty. I want to see how they do the caching though, want to learn a bit more about that.

No u dont. You can create your .html files and put them in your include directory.
Then use $file = file_get_contents ( 'layout/mylayout.html', FILE_USE_INCLUDE_PATH );

They $file var now contains your html file and with a simple preg_replace you can populate it with the desired dynamic data.

Just make one master html file and throw the rest of the data in it. :)

I have never tested smarty's cache, so i dont know if it is very usefull. Especially when your pages are very dynamic.

Could of additions I would suggest
1. dates for sure
2. isDeleted Boolean (that what you don't neet to delete the row just update)
3. if using your first instead of delimiter I would put in a json string

Just thought hope it helps