MySQL DB

[td|35mm]

Which one of you will be good at organizing/building a proper database in MySQL?

I need some opinion for something that I'm trying to create.

 

[Gump]

I've done a bit in school but we'll be getting into it more when school starts up next week so I might be able to help

 

[td|35mm]

if you did a bit ... might not help lol. i don't need help with sql commands, I need help with a logical structuring of tables in a database.

I'm basically trying to create my own custom image gallery (I'm sick of the problems with the freebies).

The image gallery has to have main categories and subcategories. Each subcategory has its own thumbnails, a bigger version of the thumbnail, description and images are grouped in individual folders - the name of the folder its based on the subcategory.

Now, categories and subcategories can be created/removed by the end user.

Now ... the way I separated my tables inside the database are something like this:

gallery table:
- gallery_id
- gallery_name


gallery_subcat table:
- gallery_subcat_id
- gallery_id
- subcat_name


gallery_images table
- image_id
- gallery_id
- gallery_subcat_id
- image_folder
- image_name
- image_description

My question ... if you would create the tables different ... how would you go about it and why?

 

[Gump]

the only thing I would consider adding is maybe keeping track of the date it was uploaded. I would probably group them by month and year if I was uploading a lot but otherwise I don't think I'd change a thing

 

[td|35mm]

hmmm ... date ... not a bad idea.
thanks gump!

any other opinions?

 

[auron]

Looks pretty legit. If you're going to make the Gallery_subcat_id on the gallery_subcat table an autoincrement or globally unique integer then you won't have to have the gallery_id on the gallery_images as well. Won't really make much of a difference except one less field to list on your joins and such, but will lead to higher subcat_id's.

I would probably keep it the way you have it though, because it'll be much easier to delete an entire gallery with the gallery_id at every level.

I'm no DB structure expert but I've worked with a SQL server daily for the last 3 years, lol. I think you'll be alright with what you have there.

Oh, actually, you said that the folder is based on the sub category name, so why don't you store the folder name at the subcat level instead of the image level?

 

[Shotgun Jesus]

FInally making that gay porn site, eh?

 

[td|35mm]

[quote1262637848=Auron]

...

Oh, actually, you said that the folder is based on the sub category name, so why don't you store the folder name at the subcat level instead of the image level?
[/quote1262637848]

I was thinking that if I list it at the subcat level I will still have to make reference to it in the image level anyway ... because I need the folder name when I build the link for the image. I was thinking that maybe that will save me some more coding ... !? Dunno. Open to suggestions anyway ...




SJ ... is not a gay website ... is your mom's website :).