One way to avoid the audio ceasing when the projectile is destroyed is to call the static method AudioSource.PlayClipAtPoint. This fires a "one-off" sound effect, without being attached to any one specific audio source object.
The downside of this approach is that you can't control the exact falloff or other properties of the sound effect, just its position and volume. If that's a problem though, you can implement your own one-shot-like method. Have a static class or singleton that keeps a pool of audio sources, and on demand configures and deploys one to play a sound with specific settings, then recycles it back to the pool once it's finished playing the sound.
To avoid storing a lot of different material hit sound effects on each and every projectile, you can define a type deriving from ScriptableObject that's something like a "Sound Set" data container. Load up this type with fields/methods to store and select the right sound effect for each material. Then you can create multiple sound sets as assets in your project folder, and share them between many projectiles. Each projectile can hold a single reference to a sound set, and delegate to it the complexity of selecting the right audio clip for the circumstance, keeping your projectile scripts focused on the work of being projectiles.
