I've tried searching for this but I don't think I have the right key words.

I'm in the process of "cleaning up" someone elses database design and I had a few questions for you guys.

The db is used in the mortgage industry so the major table is the "loans" table. Right now this loans table has about 240 fields in it, needless to say this is too many. My question is this, when I start reducing the fields (i.e. removing all borrower fields and creating a borrower table) When do I create a relationship table and when do I create a foreign key field?

I think that for the borrower example a relationship table would be better cause I can have LoanID, BorrowerID, CoborrowerID and this way I have Loan -> Borrower -> Coborrowers (where there can be multiple coborrowers, I think this would be a recursive lookup or something like that). I also have some other things like Property, Title Company, Branch, Loan Officer, etc. where I would like to create seperate tables, but the same question remains when do I use a relationship table (hopefully I use the term correctly, meaning new table LoanLoanOfficer, with Pk, LoanID, LoanOfficerID) and when do I use a FK in the loan table.

Any help, advice, or suggestions would be welcome.

There are essentially 2 kinds of relationship: one-to-many (1:m) and many-to-many (m:m). (There's also 1:1, but let's ignore that for now).

1:m relationships are implemented using a foreign key column and constraint on the "many" table. For example, if an employee is always associated with only 1 department and a department can be associated with many employees (department 1:m employee) then we add a deptno column and a foreign key constraint to the employee table.

m:m relationships are implemented using an "intersection table" which has a foreign key to each of the related table. For example, if an employee can be associated with many projects, and a project can be associated with many employees (employee m:m project) then we create a new table e.g.
assignments(assignment_id primary key, empno references employee, deptno references departments)