ext4 and other Linux-native filesystems support extended file metadata, where you can put that information right in the file. I think KDE's Dolphin file browser has support for tags using these.

What you're essentially asking for, though, is how to make a database that correlates those assets with some metadata.

One solution is to use the filesystem as a "poor man's" database, like you suggest. I would recommend using symlinks for this: instead of copying the files, you just reference them in multiple locations. This has the advantage of all your tools supporting it out of the box, since they're just regular files on the filesystem

Another is to use a text document of some format, essentially a spreadsheet, that matches metadata with file path.

You could use SQL with a real database. This is a heavyweight and very powerful tool, might be overkill.

Finally, there are probably third party tools geared towards this already. Think of something like itunes but more general. You'd probably be able to find them by searching for "Music tagger" or "photograph tagger".