• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

What is the best way to organise photos for a multidiscipline database

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?
0
Sheils
Asked:
Sheils
  • 4
  • 4
  • 2
  • +2
3 Solutions
 
theSAPPHIRECommented:
I've been writting databases and programming for over 25 years..

I find the simplier, the better..

Your option 1, seems perfect to me..

I can call that table anytime I want, and get any picture I want, within the database in any area.

I've always like the "modular" approach...  never duplicate... start with a core database, and add "modules" around it, that use the core for a foundation, and the modules define the "areas"..

1 for, Shipping, 1 for Residents, 1 for Business, 1 for Cemetery, 1 for Monuments, etc...etc..etc..

You can link them together with KEY - ID's...

Simple tables, seem to work the best, in my opinion.

-sapphire
0
 
Neil RussellTechnical Development LeadCommented:
Option 3.....
1st table, tblPhotos { PhotoID,PhotoPath}
2nd table, tblTags {TagID, Tag Description }
3rd table, tblPhotoTags (PhotoID,TagID }

Now you just create an entry in tblPhotoTags for each "Tag" that applies to a photo (Ship, Monument, Person) etc
0
 
SheilsAuthor Commented:
Thanks Sapphire. Looks like we are on the same path.

Nielr, I am trying to digest your approach. If I understand this correctly there will be only one table for all the area and the tadid will be used to define the area. I guess we will also need a fldEntityID(eg shipid,personid or monumentid) in tblPhotoTags.

Is that what you meant or have I misunderstood.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Neil RussellTechnical Development LeadCommented:
It is not a complete solution, just another example of the approach.  Personally I would not be having a DIFFERENT table for every type of entity in your system (People, buildings etc...) Rather one table for enteties and others for very specific properties.
Thats what tblTags is, your entities. A Tag is a person OR a building OR a location etc
0
 
KGNicklCommented:
I would keep the photos in a single folder.

Have a script that handles the upload/import as well as the edit/delete/etc..... of photos. When uploaded the file will be renamed a unique number and copied to the folder with the images. The table will store all the info.

TABLE: PHOTO
FILE_ID (Number) - Primary Key, this uniquely identifies each file and is also the name of the file (excluding the file extension).
FILE_EXT (VARCHAR2) - .jpg, .png, etc...
NAME (VARCHAR2) - What the actual photo should be called (original name). Such as "nice painting", "front door", etc....
DESCRIPTION (VARCHAR2) - optional... (would be the description of the photo)
UPLOAD_DATE (DATE) - optional... (would be when the file was uploaded)
EDIT_DATE (DATE) - optional... (would be for when the information or photo was last edited)
FILE_DATE (DATE) - optional... (would be when the file was taken)

TABLE: PHOTO_TAGS
TAG_ID (Number) - Primary Key, you don't really need to have this for this table, but can be nice to have.
FILE_ID (Number) - Same unique value that is in the PHOTO table. Not unique in this table...
Tag (VARCHAR2) - shipping, residents, businesses,cemetery records,monuments, etc....

So your photo table holds all your photos in one place. Each photo should contain the same basic details, so no reason to create additional tables. Each photo does differ in the tags it can have and they can have multiple tags. So the tags table can have multiple rows for the same photo, each being a different tag.
0
 
SheilsAuthor Commented:
KGNickl,

Nice idea too. But for two problems that you may have a work around for:

1) Users may try to upload a photo that is already in the image folder.Noting that at present every one have some photos on their desktop many of them duplicates, it is likely that the guy entering the ship info will upload the photo of a ship then the guy doing monuments will upload the same photo as the ship that brough in the material. I was going to try and get around that by running a search for file with the same name in the image folder before adding a new photo. How do you work around this if you change the name of the photo when you upload.

2)The description of a photo may be different in different areas. Eg: In the ship table the description photo of a boat may be boat name taken at location x on date. In monument table the description boat name frieght the timber used to build the monument.

Guys,

All your ideas are very good, I am trying to bring them togeether, just need to churn through some of the problems that may arise in the furure.
0
 
Neil RussellTechnical Development LeadCommented:
In that case extend on my option 3 idea but....

3rd table, tblPhotoTags (PhotoID,TagID, ThisDescription }

Now you have a seperate description for each Tag->Photo relationship

0
 
Neil RussellTechnical Development LeadCommented:
HMmmmmmm

TABLE: PHOTO_TAGS
TAG_ID (Number) - Primary Key, you don't really need to have this for this table, but can be nice to have.
FILE_ID (Number) - Same unique value that is in the PHOTO table. Not unique in this table...
Tag (VARCHAR2) - shipping, residents, businesses,cemetery records,monuments, etc.... MASS Duplication!!

NO!!

========== Yes ============
TABLE: PHOTO_TAGS
PHOTO_ID (Number) - Same unique value that is in the PHOTO table. Not unique in this table...
TAG_ID (number) - Same unique value that is stored in the TAGS table. no unique in this table.

Primary key is the combination of BOTH those fields.

0
 
KGNicklCommented:
So I would suggest just moving the name and tag fields to the tags table.

As far as duplicates I wouldn't worry about it. It hard to avoid duplicates with photos. Assuming you have a duplicate and the duplicate doesn't repeat tags no one would even know. Then if you did notice it you could just add the missing tags to the original then delete the duplicate.

How many photos are you planning on having?
Will it grow over time?
Will it stay steady (as some are added old ones are removed)?
What enhancements could you imagine someone asking you to include in the future?

Most of the options selected would work, just how good they will work, how flexible they are, etc.... will be a factor. If there are going to be tons and tons of photos that keep growing you will not want a single table and to store them in separate tables (and most likely separate folders).

If your not going to get to large the single folder and single table for all the photos would work and be easy to manage.

Something to ponder: Remember CPU power is expensive and slow (Larges databases with large tables take longer and use more CPU power). Disk space is cheap, so does it really matter. So you might just want to create a table for each area and have them manage the images separately. Smaller tables, image replication (takes up more disk space, but who cares disk space is cheap), less chance of groups messing each others stuff up or butting heads.

Its really up to you to grab one or a combination of the ideas here that work best.

Once you get an idea post up the design and everyone can critique it. By the end you will have something that should be clear of the obvious bad designs flaws that are easy for one person to overlook.
0
 
SheilsAuthor Commented:
Hi Guys,

I will get back to you on this tommorrow, need to go to my day time job.

Cheers
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
I agree with -sapphire that option 1 is the best method.

FWIW: I use the Web Browser control for display  lots of  different file types on a form.

The way I handle this is to use a single junction table to relates a record  to multiple tables

Here are some sample database that has the tables I use.

Document Links 2

This is an update to the basic example of how to store the path to a file and also be able to view the file. You can browse using the standards windows common dialog to select the file. You can view the file using the application defined with the Windows File Associations. It uses the ShelExec API to open the file. It does not use the .Followhyperlink  method.

This example is a continuous form that could easily be used as a sub form to attach multiple documents to a record.

***** Because it doesn't use the hyperlink data type, this new version stores the path in a way that allows the folder to be easily moved.


Document Links

This is a basic example of how to store the path to  a file and also be able to view the file. You can browse using the standards windows common dialog to select the file. You can view the file using the application defined with the Windows File Associations. It uses the ShelExec API to open the file. It does not use the .Followhyperlink  method.

This example is a continuous form that could easily be used as a sub form to attach multiple documents to a record.






0
 
SheilsAuthor Commented:
Thanks Guys. I will amalgamate your idea and mind which will surely gennerate a better outcome than my original idea
0
 
theSAPPHIRECommented:
glad we could help...

-have fun

-sapphire
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now