Dynamically Load Resources As Needed

If I have hundreds or thousands of prefabs, I don't want to preload everything when my scene is loaded because A) it will likely consume a ton of memory that isn't necessarily needed, and B) it will take a very long time to load the scene when I only need a very small subset of those resources right away.

I'm trying to design a way to dynamically load resources as needed. For example, you defeat an enemy and he drops some new weapon which you decide to equip, so the prefab for that weapon is loaded.

What I'm trying to figure out is if the Resources.Load() method keeps track of what has already been loaded and just returns the existing resource if it's already loaded. If so, can I just call Resources.Load() each time I need to load a resource and then GameObject.Instantiate() to instantiate it?

Or would it be better to create a class that manages the dynamically loaded resources? I could have it call Resources.Load() if a needed resource has not been loaded yet, or just return the requested resource if it is already loaded.

Or is there a better approach? What have others done to solve this problem? This is more of an open ended design question.