This is more of an open ended question regarding design best practice.
Say I have two classes; one is a Vehicle, and the other is an InformationSource (for maintenance/repair information). An information source will be used for many different vehicles, and a vehicle may have multiple different information sources (for different parts of the vehicle).
Vehicle and InformationSource are two different tables in the database, with a seperate link table to map them together (VehicleInfoSourceLink).
I currently keep a cache/collection of Vehicle objects for a specific customer (All vehicle records with customerID = X). And I also keep a cache/collection of InformationSource objects for a specific customer.
However when it comes time to find the available InformationSource(s) for a vehicle - I am not sure what the best way to do this is.
Option 1) Create a new object in my C# code that represents the link table e.g. VehicleInfoSourceLink and keep a cache of this available along with the Vehicle and InformationSource caches. This way when I have a specific vehicle, I can look at the link objects and find all the InformationSource Id's for a specific Vehicle, and then select those Ids from the InformationSource cache.
Option 2) Create an SQL function called "GetInformationSourcesForVehicle" which takes in the id of the vehicle and returns the Id's of all InformationSources that are mapped to it. I would call this function from my C# code, and keep a list of IDs, and then select the InformationSource objects with those IDs.
Really keen to hear peoples opinions of which is the better option, or maybe other solutions as well. Open to any feedback including how to ask questions like this better (as I want to use forums like this more in future to get good design advice).