I'm trying to determine when a web application should query a database for related data that may or may not be used in the current request.
As an example, consider a database that tracks assets for a company. There is an Assets table, an Owners table, and a Users table. Assets may be assigned to an OwnerID, and Asset records will track the UserIDs that created, updated, and deleted the Asset.
When doing web requests for the data, I see two main options:
Read all of the data in one request, with a join to Owners and multiple joins to Users (user created/updated/deleted). While this makes for a heavier query, it would reduce repeated calls to the database. It also simplifies the use of OOP within the application, as every entity will be fully populated and can be accessed easily, e.g.: Asset.Owner.Email, Asset.LastUpdatedBy.Name, etc.
Only read the data from the Owners table up front, and create separate database requests when the application requires any Owner or User information other than the ID. In about half of the HTTP requests, no extra info will be required, and in the other half of the HTTP requests, the application will need to query the name, email, etc. of the Owner and each of the users who created, updated, or deleted the asset. I like the idea of the lean initial request, but I worry about performance when doing an additional handful of requests to get the related data. It also complicates the OOP design in the application somewhat; instead of being able to simply retrieve Asset.Owner.Email, I'll have to first check if the Asset.Owner object has been populated, call Asset.LoadOwnerByID(Asset.OwnerID), then finally access the value. (Ignore the fact that Asset shouldn't have a LoadOwnerByID method - that's just an example.)
Now that I've written it out, I'm leaning towards Option 1 as being the most efficient and simple way to do things. But it gets a little less obvious when you add more recursive relations to the data, e.g.: Each user has a ReportsToUserID value pointing to another User, and each Owner has an AccountManagedByUserID field pointing to a User.
In these examples, what is your preferred way to do things, and what have you found to be the most efficient, both in terms of performance and programming complexity?
PS - I'm intentionally leaving out specifics on web framework and database type because I have had this question come up in all environments, from ASP.Net/MSSQL to Java/SQLite to PHP/MySQL, and I believe the principles will generally apply to all of those environments.