Database schema

Hi,

I’ve been struggling with a good database design for a portfolio project.

Especially how to setup the tables and connections for the images.

A PROJECT has one main image, and contains a gallery which contain collections of multiple images.

So far I’ve got this setup: www.jelmercnossen.nl/schema.png

My reasoning is that:

A project HAS_ONE gallery

A gallery HAS_MANY collections (image sets)

A collection HAS_MANY images

Can someone help me out with tables and primary and foreign indexes needed?

For example should I have a table in-between with compository primary keys etc.

thanks

Personally, for image ‘collections’ I would introduce some kind of ‘tag’ system.

One image may belong in many ‘collections’ or categories. For example, an image of a butterfly perched on an orchid could belong to many categories such as ‘Butterflies’, ‘Insects’, ‘Orchids’, ‘Flowers’…

Furthermore, if the photo was taken in Greece, that’s yet another category.

I don’t know your specific requirements but if you are interested in this approach there is some good information in the link below. You might do well to skip to the ‘Recommended Architecture’ half way down the page.

Tag Schema

Unless a project contains lots of data unrelated to its gallery, then the project IS the gallery; therefore you don’t need the project table, which simplifies things. I came up with this example real quick so it may not be correct but here it goes…

GALLERY


id INT - primary key

title VARCHAR - should be indexed unique to avoid confusion

main_image VARCHAR - URL or pathname of gallery’s main image

client_id INT - foreign key to CLIENT table

category_id INT - foreign key to CATEGORY table

… other table columns as neeeded

IMGCOLLECTION


id INT - primary key

title VARCHAR - index unique not necessary but useful

gallery_id INT - foreign key to GALLERY table

… other table columns as neeeded

IMAGES


id INT - primary key

title VARCHAR

imgcollection_id INT - foreign key to IMGCOLLECTION table

img_filename VARCHAR - URL or pathname of image

… other table columns as neeeded

If you’re going to add a tagging feature then you’ll need 2 more tables:

TAGS


id INT - primary key

title VARCHAR - should be indexed unique to avoid confusion

… other table columns as neeeded

IMGTAGS


id INT - primary key

tags_id INT - foreign key to TAGS table

images_id INT - foreign key to IMAGES table

I hope this helps. Good luck.