I've been using Apple Photos, and before that, Apple iPhotos, to manage my, er, photos for a long, long, time, likely back to their introduction in 2002. Over that time I've made a few libraries, for family and some for other things, which at points I've merged back together to make a larger single library.
However, a few months ago I was checking my SpiderOak backups, which basically takes a backup of the 'Masters' folder inside the photo library bundle, which is where the original photos are kept nowadays, and I noticed that I had no 'year' folders for 2001-2003, but I knew that I had photos from those years as they were viewable as normal in the application. Pokling around further, I found whole missing month ranges, and quickly looking at folder sizes, some months and days were disproportionately huge. Note here that I generally only looked at the backup at the Masters folder size level, which looked fine most times.
I then used the excellent EXIFtool to review the EXIF creation dates of my photos in the masters folder for each year, and output it to a .csv file. From this I learned that these files from 2001 for example were in a sub-folder of 2005, which led me to really take a look at where iPhoto/Photos had been keeping all my photos. The EXIFtools output suggested that I had photos in different years, in sub sub folders, differently named folders, structures, and more permutations.
It appears that over versions Apple has altered the photo organisational file structure and naming conventions. Sometimes and currently it was a heirarchy of folders as 'year > month > date', which is fine, but sometimes it seems to have been a date as a single folder such as 'Dec 26, 2009', which gets interesting when some folders would be called 'Dec 26, 2009_2'. Another thing I noticed is that some folders would be named after the newest photo in that folder, but there could've been photos in there from weeks earlier, potentially since a previous import. I've also had folders which had photos which the application seemed to have read different parts of the EXIF, or had some EXIF entries missing - my thoughts here are that some cameras just don't seem to do a very standard EXIF write, such as early camera phones and some of the kids cameras (I'm looking at you, LEGO camera).
Also, 'events' in some versions it seems got their own named folders, so I'd have some folders called 'Family January 2009' which would be seperate from any other folder about January in 2009.
As I mentioned, I have at times also imported (merged) libraries in (i)Photos. It would seem that at least twice, it imported a whole library into the date in the structure when I imported it, and created whole sub folder structures within that day, ignoring at that point, the date of then photos.
For example, if I imported a library on August 15th, the expected folder for that day would have folders within it with folders called '20110911-002348' with photos from different dates in the preceeding 3 months of September 11th. This seems to have started in 2011 or perhaps 2010? This mean't that a whole imported library was in a single days folder, in my case sometimes hacing years worth of photos for 2002, in a folder in 2011.
I also noticed some duplicates - somehow I had ended up with 2 identical copies of a photo. Sometimes one was rotated, one was not, or one had its file endinf in '.JPG' and one with '.jpg'. These were with very old files, so I assume Apple made the addition file to keep an original and rotated.
My current and broad theory is that Apple for quite a while, has stored the photos in folders as to when they were imported into the application, ignoring the EXIF or other file details. I don't blame Apple for this, by the way, as I've alway let Apple manage the back end and honestly never thought about it and did that horrible thing of assuming it was consistantly managing them by creation date with all the other meta goodness like albums, events, etc done at a higher metadata level. It seems that hasn't always been true.
What is the practical upshot of this? Well, whilst I use Apple Photos, all is well, it still looks and works just fine. However, tracking any backup issues could be problematic, in SpiderOak, but also potentially in TimeMachine. It also means that if I moved to another application, like LightRoom and Photo Mechanic, whilst it could likely figure it all out on EXIF, it would be messy, and increasingly would make it difficult to give photos to people, or remove photos without making mistakes at the file level.
Well, I've left the Apple Photos library as-is essentially, and will continue to import photos into it for now. However, I have also made a copy of the Masters folder, and with EXIFtool and a little scripting, created a simple 'Year > Month > Day' folder structure, and moved everything into the 'correct' folder based on photo creation date. This isn't the prettiest solution, but it also gave me the opportunity to sanitise a few things and meant I could delete a lot of photos of nothing really. I repeat this every 3 months, and so far it's working really well.
I'm tempted to start a new Photos library, and import this cleanup folder structure I've made, and see how it deals with that, and I'll update here if that's a decent solution. I may also try PhotoMechanic or some other app as the Mac is 8 years old now, and even with an SSD isn't so fast with some of the photo manipulations so I may move all the photos to a newer machine, which may or may not be a Mac.