Intranet Database Advice Please

[JohnBoy2005]

I'm setting up an Intranet at my school and part of it will host a photo gallery, with about 10,000 pictures (and growing).

I am using an Access Database to keep the information on each photograph and sorting which gallery they belong.

What's gonna be the best;

A table for each gallery or one table holding all data.

Any advice welcome.

Many Thanks

John

 

[gmmastros]

I would suggest neither.

First of all, I strongly encourage you to spend a couple minutes research database normalization techniques.

[google]Database Normalization[/google]

I would suggest one table that contains picture information. You should have another table to store gallery names, etc...

For example:

Pictures
--------
PictureId
FileName
GalleryId

Gallery
---------
GalleryId
GalleryName

Suppose you have 20 different galleries (Basketball Game, Graduation Ceremony, Honor's Society, etc...). Each gallery would get a unique id (GalleryId). Then, in the pictures table, you store the ID (not the name). This will help reduce the size of your tables, which will improve performance.

This also allows you to expand functionality later without having to mess around with a bunch of tables. For example, suppose you want to store the photographer. All you need to do is add another table, Photographer, with a PhotographerId and a PhotographerName. Then add another column to the Pictures table for PhotographerId.

After you do a little research on database normalization, you should realize that most professionally designed databases are 3rd order normal.

I once heard.... "Normalize until it hurts, denormalize until it works."

Words to live by!



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JohnBoy2005]

Thanks for the advice.

I'd already started to go down that route, wasn't sure it was the right one though.

Totally re-assured now.

Cheers