In my application, one part involves users creating projects, uploading maps (images) to that project, defining territories on those maps, and then defining borders between those territories. For arguments sake, let's use this naming schema:
- A map is referred to by ProjectID
- A territory is referred to by TerritoryID
- A territory for a specific map is referred to by ProjectTerritoryID
- A border between two territories is referred to by ProjectTerritoryBorderID
Now, since I'm following the convention of 'Desing by Contract', I need to make a few checks when a user wants to add a new border between territories. The checks are:
- Does the project exist (i.e. - does the ProjectID exist in the DB)?
- Does the user own the project (i.e. - does the UserID exist against the ProjectID)?
- Does the source territory exist (i.e. - does the TerritoryID exist in the DB)?
- Does the target territory exist (i.e. - does the TerritoryID exist in the DB)?
- Does the source territory exist in that project (i.e. - does the ProjectTerritoryID exist in the DB)?
- Does the target territory exist in that project (i.e. - does the ProjectTerritoryID exist in the DB)?
- Finally, check whether the territories already have a border (i.e. - does the ProjectTerritoryBorderID exist in the DB)?
Currently, I am performing these checks within a singleton connection to the DB. There is, in total:
1 DB connection
5 prepared statements
7 executions of prepared statements
Each of these statements is extremely lightweight, generating a COUNT(*) of the matching rows, with zero count meaning the check has failed.
I have a second option - query the database once, generating a model of the project that has information on all users and territories attached to that project (i.e. - generating an associative array of many rows). This query is not so lightweight, as a project with 2 users, 100 territories, and 300 borders will generate 2*100*300 = 60000 rows of data.
Now, while I'm pretty good with database querying and design, I'm not so sure on the administration side of things. Does one connection with 6 queries generate 6 threads, for instance? Should I even be concerned over how many queries 1 DB connection is sending? Is it feasible to pass some of the checking to PHP, or should I stick with my mantra of "let SQL do the heavy lifting"?
This post has been edited by e_i_pi: 19 January 2012 - 03:01 PM