I am just getting started on this complex query I need to write and was hoping for any suggestions or feedback regarding table structure and the actual query itself..
I've already created my tables and populated test data, and now just trying to sort out how and what is possible within MySQL.
Here is an outline of the problem:
End result: Listing of rates based on specific queried criteria (see below):
Age: [ 27 ]
Spouse Age: [ 25 ]
Num of Children: [ 3 ]
Zip Code: [ 97128 ]
The problem I am running into is each company that provides rates has a unique way of dealing with the rate. And I am looking for the best approach for multiple queries based on the company (one query with results for each company more or less all combined into one result set).
Here are some facts:
- Each company deals with zip code ranges which assist in the query result.
- Each company has a different method of calculating the rate based on the Applicant, Spouse, Num of Children: Example,
a) Company A determines rate by: Applicant + Spouse + Child(ren) = rate (age is pertinent to the applicant within a range).
b) Company B determines the rate by total number of applicants like: 1, 2, 3, 4, 5, 6+ = rate (and age is ignored).
First off, what would I call this type of query? Multiple nested query? And should I intertwine php within it to determine the If()s ...
I apologize if this thread lacks sufficient data, so please tell me anything you would like to see.
