Is their a way to condense my table?

[marra1]

I am working on a database with interest rates for over 50 different automotive lenders. The goal is to have a user type in : Credit score (ex: 689). Year of car (ex: 2003), loan term (ex: 72 mos.), Amount financed (ex: $24500), loan to value (ex: 103%).

My dilemna is this. Bank "A" has the same rate for credit score between 999 - 730 for year model 2003 - 2005. for terms 24 - 60 months. The same rate for 729 - 730 year model 2003 - 2005 for terms 24 -60, etc. They also give .25% discount for fincnacing over $25000 if your score is over 700 and .25 off if your loan to value is less than 90% regardles of score.

I originally started listing this out one by one.
(Ex:
-- Server version3.23.58-log

--
-- Table structure for table `rates`
--

CREATE TABLE rates (
id int(11) unsigned NOT NULL auto_increment,
year int(11) NOT NULL default '0',
lender text,
term int(11) NOT NULL default '0',
score int(11) NOT NULL default '0',
base_rate decimal(3,2) NOT NULL default '0.00',
spec_disc decimal(3,2) NOT NULL default '0.00',
equity_disc decimal(3,2) NOT NULL default '0.00',
lux_disc decimal(3,2) NOT NULL default '0.00',
other_disc decimal(3,2) NOT NULL default '0.00',
low_ltv decimal(3,2) NOT NULL default '0.00',
eff_date text,
timestamp datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (id)
) TYPE=MyISAM;

--
-- Dumping data for table `rates`
--

INSERT INTO rates VALUES (1,2005,'Bank of America - Fast Lane',24,900,4.55,0.25,0.40,0.25,0.00,90.00,'10-14-04','2004-11-12 16:55:52');
INSERT INTO rates VALUES (2,2005,'Bank of America - Fast Lane',24,899,4.55,0.25,0.40,0.25,0.00,90.00,'10-14-04','2004-11-12 16:55:52');
INSERT INTO rates VALUES (3,2005,'Bank of America - Fast Lane',24,898,4.55,0.25,0.40,0.25,0.00,90.00,'10-14-04','2004-11-12 16:55:52');
INSERT INTO rates VALUES (4,2005,'Bank of America - Fast Lane',24,897,4.55,0.25,0.40,0.25,0.00,90.00,'10-14-04','2004-11-12 16:55:52');
INSERT INTO rates VALUES (5,2005,'Bank of America - Fast Lane',24,896,4.55,0.25,0.40,0.25,0.00,90.00,'10-14-04','2004-11-12 16:55:52');

I had over 10,000 rows for just my first bank, and updating this would be a nightmare. Any ideas on a better way to do this?

Any help would be greatly appreciated.

[uncle_bob]

Instead of trying to store every possible combination in a database, I'd recommend writting a function for each bank that calculates what you want, based on their specific criteria. I'd guess that many would be similar if not the same.

This problem looks like it more needs a coding solution than a db solution.

[mincklerstraat]

Welcome to Webmasterworld [webmasterworld.com], marra1.
You could have two fields for the credit score, a min_score and a max_score, in this case 730 and 999 respectively. You then replace 269 rows with only one. Just a question of modifying your table structure slightly and writing your query a little differently.

[marra1]

This board ROCKS! Thanks so much for your input. I put this same post on another board and it has been 2 days without a single reply.

This is exactly what I am looking for. I could also apply a min and max to for loan term.

I might be back later when I am having trouble extracting the info I need.

Thanks again!