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,ceme
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,fldD
2) Create a folder hierarcy (eg: ships,people,monuments,...
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?
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,fldD
2) Create a folder hierarcy (eg: ships,people,monuments,...
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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
Thats what tblTags is, your entities. A Tag is a person OR a building OR a location etc
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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
3rd table, tblPhotoTags (PhotoID,TagID, ThisDescription }
Now you have a seperate description for each Tag->Photo relationship
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.
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.
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.
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.
ASKER
Hi Guys,
I will get back to you on this tommorrow, need to go to my day time job.
Cheers
I will get back to you on this tommorrow, need to go to my day time job.
Cheers
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Guys. I will amalgamate your idea and mind which will surely gennerate a better outcome than my original idea
glad we could help...
-have fun
-sapphire
-have fun
-sapphire
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