SQL efficiency argument, add a column or solvable by query?
- by theTurk
I am a recent college graduate and a new hire for software development. Things have been a little slow lately so I was given a db task. My db skills are limited to pet projects with Rails and Django. So, I was a little surprised with my latest task.
I have been asked by my manager to subclass Person with a 'Parent' table and add a reference to their custodian in the Person table. This is to facilitate going from Parent to Form when the custodian, not the Parent, is the FormContact.
Here is a simplified, mock structure of a sql-db I am working with. I would have drawn the relationship tables if I had access to Visio.
We have a table 'Person' and we have a table 'Form'. There is a table, 'FormContact', that relates a Person to a Form, not all Persons are related to a Form. There is a relationship table for Person to Person relationships (Employer, Parent, etc.)
I've asked, "Why this couldn't be handled by a query?" Response, Inefficient. (Really!?!)
So, I ask, "Why not have a reference to the Form? That would be more efficient since you wouldn't be querying the FormContacts table with the reference from child/custodian." Response, this would essentially make the Parent is a FormContact. (Fair enough.)
I went ahead an wrote a query to get from non-FormContact Parent to Form, and tested on the production server. The response time was instantaneous. *SOME_VALUE* is the Parent's fk ID.
SELECT FormID
FROM FormContact
WHERE FormContact.ContactID
IN (SELECT SourceContactID
FROM ContactRelationship
WHERE (ContactRelationship.RelatedContactID = *SOME_VALUE*)
AND (ContactRelationship.Relationship = 'Parent'));
If I am right, "This is an unnecessary change." What should I do, defend my position or should I concede to the managers request?
If I am wrong. What is my error? Is there a better solution than the manager's?
