I'm also trying to organize and track footage overal several years, not just a single project. For example if I want to see all photographs of a certain girl dancing over the last 5 years I can get that in about 10 seconds in lightroom, but how can I do that with my video footage??
Don't work in raw video for dynamic archiving. It works for photographs because they are
small files. It also works for music because songs are small files. Video is rarely small files. HD Video even more so.
For the dancing girl analogy, suppose she has dance events that you capture on video from time to time over that 5-year span. Edit that video out as either separate clips and render them as small final files. Then, tag them with a poster image, description and even search terms (her name, dance, dancing, etc) and put them in a folder ("EllensDanceVideos" or "Dance", etc). If you need to mix & match them in future events, go back and work with those renders (splice them together, mix them with other video, etc). All very easy to do.
I've got hundreds of "home movies" I've shot on AVCHD equipment over the last few years organized exactly this way. I split them out by year and name the files with names so that I know what video is rendered within them. I tag them with MetaX or MetaZ with a unique poster, a good description of their contents and many other details typical of iTunes video metadata (making them easy to sort & find via search). Yes, I also hang onto the masters (the original AVCHD file structure) should I ever need to go back to them for future editing work, but the edited & rendered files are easy to organize, search, etc.
OR, merge all of the video clips of her dancing into a single file and maybe use Chapters to identify one shot 5 years ago from one shot 4 years ago, etc. If she has some more dancing events in the future, you can just append new shoots onto these older renders.
As previously described, you're trying to organize in raw, unedited footage. Take the next step. Edit it into final renders (after editing) and then tag those rendered videos with everything you need to quickly locate them anytime you want.
OR, load up on hard drive storage (HUGE, HUGE storage) and read all of those raw clips into editors like FCP X in which you can then tag raw clips and have them all dynamically available (if you have enough storage for all of them) to mix & match as you see fit.
But the easy answer continues to be going from large, unedited raw files to edited and rendered smaller files. You'll be taking raw video and getting it into the consumable form... just like a collection of photos or music. The latter just doesn't take the same amount of processing by nature of how the raw files are generally the consumable form (or quickly become the consumable form on import).
If you just can't bring yourself to go in one of these ways, the next best option is to follow the film archival model. Open up a program like Word, enter each file ID name in the AVCHD stream folder, watch it with VLC and document its contents in that Word doc. Then, you can use the Word doc as a searchable doc to locate specific raw clips that contain whatever you want to find. A more sophisticated version of this would be to build your "search" database in Excel or Numbers or- even better- a database program.
If you really want an iPhoto-like experience, render each raw file as a small Quicktime video. Drag that into iPhoto and tag it like you can tag a photo. iPhoto will play video files. Name these files with the same names as the raw video (AVCHD) names or put the raw video names in the "notes" field. This won't be archiving the raw video itself but small file proxies of the raw video (much like the thumbnails vs. underlying bigger photo files used in iPhoto). However, this would be pretty close in functionality to what you seem to be seeking- an at-a-glance video clip-based database of all clips shot with AVCHD.
Yes that won't be as easy as just importing photos into iPhoto but it shouldn't be. Why? Because video is not a single shot but many shots per second of various subjects, scenes, times, etc. Thus whether you choose the film archival model or this iPhoto proxy approach, either will still need someone to watch the clip and type a description of it into tags for subsequent search purposes.