Database Design Help

focus310

Hello:

I was wondering if someone can help me out with a database design. I could use a second pair of eyes to check whether I normalized my data correctly.

This is an application for a mortgage broker who wants to display rates for various loan programs and lenders. His loan processor will need to update the rates for all programs and lenders.

To make things easier, I'm going to use small numbers.

The broker is offering two loan programs: 30 Year Fixed and 5/1 ARM.

The broker has three lenders: National City, Wells Fargo and Flagstar.

Each loan program and each lender will have four interest rates entered.

It would look something like this:

Program | Lender | Rate

The objective this application is show potential borrowers the lowest rate from each lender for each program.

Thus far, I have three tables created:
loan_program: id, program
lender: id, lender
program_lender: id, program_id, lender_id

The data for the tables look like this:
loan_program:

id | program

1 | 30 Yr Fixed
2 | 5/1 ARM

lender:

id | lender

1 | National City
2 | Wells Fargo

program_lender:

id | program_id | lender_id

1 | 1 | 1
2 | 1 | 2
3 | 2 | 1
4 | 2 | 2

The third table (program_lender) is a composite key associating a program with a lender.

I now will need a rates table which will contain the rates for each lender and program. So far, the table I think I should have looks like this:

rates

id | program_lender_id | rate

1 | 1 | 5.000
2 | 1 | 5.500
3 | 1 | 6.000
4 | 1 | 6.500
5 | 2 | 4.000
6 | 2 | 4.500
7 | 2 | 5.000
8 | 2 | 5.500

Can someone review my tables and let me know if I did the tables correctly?

Thank you so much.

tomhath

I don't see that you need the "rates" table. There's nothing wrong with it, but you could just as well have the rate column on the program_lender table. The small amount of normalization you get by making it a separate table doesn't seem worth the trouble to me.

But I assume this is a classwork assignment of some kind, so there might be other reasons to have it fully normalized.

focus310

Hi,

Thank you for the reply.

This is an actual application not a school project. I originally stopped my design with the third table (program_lender) where I added the rates. For whatever reason, I thought I had to normalize further.