manytomanytomany

[sermac]

Hi OOADForum Experts:
How can I normalize the following: I have loan application with my clients charcteristics. i.e. credit, income,assets etc. etc. There is over 200 different "programs" available amongst many different wholesalers. . .and I want to build a program search to find the "best" program for the client's desires. . .credit, income etc.

CREATE TABLE Transact(TransID n(2) PRIMARY KEY,transtype C(20))
INSERT INTO Transact(TransID,transtype VALUES 101, "Purchase")
INSERT INTO Transact(TransID,transtype VALUES 102, "Refinance")

CREATE TABLE Occup(OccupID n(2) PRIMARY KEY,Occuptype C(20))
INSERT INTO Occup(OccupID,Occuptype VALUES 201, "Owner Occupied")
INSERT INTO Occup(OccupID,Occuptype VALUES 202, "Non Owner Occupied")

. . .more. . .

Create Table Lien C(10)
Insert Values "First Mortgage"
Insert Values "Second Mortgage"
Create Table AmortFixed C(10)
Insert Values "30 Year Fixed"
Insert Values "25 Year Fixed"
. . .etc.
Create Table Program c(10)
Insert Values "FN30-360"
Insert Values "FHA15-180"
etc.. . .200 plus. . .


Join tables
Trans_amortfix
Trans_occup
Trans_lien
Trans_Prog
Occup_amortfix
Occup_lien
Occup_Prog
Lien_prog
Lien_amortfix
Prog_amortix
. . .
some data. . .
1) purchase,ownerocupied,firstlien,30yrfixed,progcodeno,whlsr. . .
2) Refinance,nonownerocupied,secondlien,15yrARM,Cashout,progcodeno,whlsr. . .

My question is how can I conceptualize what I am trying to do? Can a person have a join table like. . .T_O_L_A_P . . .

its almost like a monopoly game where one move begets another . . . ( hope this makes sense)

Any feedback or thoughts will truly be deeply appreciated.

THX

Bill

 

[dragonwell]

Have you done an entity or UML class diagram?

Fist, I would begin by getting a domain model. This way you can identify the major concepts / entities and their relationships (and operations), without the notions of tables, keys and joins getting in the way. Then, once you have the idea of what's what in mind, it will be easier to map it to tables in a relationsional database.

Then goal at this point is to make sure you keep the proper perspective (not too general or too detailed) in order to realize the domain in the most logical way.

I find that once the entities are identified from a domain/object-model perspective, designing the schema store the data should come easily.

Hope that helps.

Greetings,
Dragonwell

 

[sermac]

Dragonwell, Thanks for your response. . .
I built a huge wall chart "tree"
building from Top down and bottom up
and building a conceptual model.
Then I started on a "logic" approach
from entities, then attributes, then keys.
and then trying to build the relationships with
cardinality, triggers,business rules etc. . . .been a steep learning curve. . .read it, then read it again, and then again,and then again. . .
Beginner question: What is a "UML class diagram"?
. . .how about Visio or something like it?
(tough to find academic classes a person can enroll in,
if you know what I mean.
Bill

 

[sermac]

. . .I did keysearch "UML" Unified Modeling Language.
. . .tons to go at
appreciate the direction.
THX
Bill

 

[stevexff]

Dragonwell's approach makes sense. It sounds like you already have a lot of knowledge about the application, and this is causing you to get bogged down in detail too early. Take a step back, and look at it from a functional perspective - 'what functionality do I have to provide?', rather than 'how am I going to provide it?'. This might help you to simplify your task.

Perhaps a scheme where you rate each of your 'programs', giving a 'suitability score' for different customer characteristics. You could then calculate similar scores for the same characteristics for each customer (maybe a boolean or small range, say 0-5). Matching the customer to the best program then reduces to multiplying the corresponding customer/program scores and adding them together. The one that scores the highest is the 'best fit' one.

As for tools, Visio is OK for diagrams, Rational is the market leader, ArgoUML is open source (and free).

Get a good 'How To' book on UML. Best to look in a bookshop, where you can see before you buy, as some of them are long on theory and short on practical advice

Steve