I am building a database for the local historical society which will cover multiple areas such as shipping, residents,businnesses,cemetery records,monuments, etc...
There are photos related to all these areas. Some phots may be related to multiple areas. Eg The photo of a boat relates to the boat itself but it can also be related to a person (owner, passenger,...) or a munument (material to build the monument was brought in by that boat.
I am trying to figure out the best way of organising these photos so that they work well with the database. The think the options are as follows:
1) Store all the photos in a single folder. Create a main photo table with field fldPhotoID, fldFilePath. The create tables for each areas that look up to the main photo table. Eg: tblShipPhoto (fldShipID,fldPhotoID,fldDescription)
2) Create a folder hierarcy (eg: ships,people,monuments,....). Then create tables for each area to store the file path. Eg: tblShipPhoto (fldShipID,fldFilePath,fldDescription).
I prefer the first option from a database point of view there will be less duplications but I am a bit worried that the folder will become too big down the line and make searching difficult. I note that the intension is that users will only access the photos through the msaccess application.
Experts, I'd like your input on this. Which one is the best? What problems can I expect down the line? Is there another approach that will work much better?