Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2015
    Posts
    4

    How do I structure this data?

    Hi I’m Jason o/

    I need to store a list of lenders which will include a name, a couple of urls and a couple of telephone numbers. I also need to store some lenders criteria such as a ‘bank_name will lend of properties built of brick’ and ‘bank_name will not lend if the borrower has missed payments in the past 12 months’ and so on.

    I am unsure how to design the database structure to store the info and although I guess I could store everything in one table I would rather segregate it out a bit as lenders will have a huge number of criteria. For example I think I’d like to separate types of criteria into separate tables like borrowers_criteria, property_criteria, employment_criteria

    Some examples:

    bank_A says mortgages must be repaid on or before the eldest applicant's 75th birthday.
    bank_B says mortgages must be repaid on or before the eldest applicant's 75th birthday.
    bank_C says mortgages must be repaid on or before the eldest applicant's 70th birthday.
    bank_D says mortgages must be repaid on or before the eldest applicant's 67th birthday.
    and so on…

    I am familiar with 1:1 and M:M associations but I am struggling to visualise how best to structure this.

    I must be able to update a lenders criteria without effecting other lenders. I should be able to bank_A.max_age = 76 for example.

    If it helps I’m building the app with Ruby on Rails and Postgres will be the database.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    it all depends on how you seek to model this

    ...its almost certainly an attribute of the bank account setup.
    but is it an attribute of the bank entity or the banks mortgage entity (assuming that a bank may have more than one loan type).
    the detail implementation is, I suspect relatively trivial. store the Age as an upper age limit and implement the business logic in the user interface. When a user wants to change a mortgage or bankjs age limit then you need to check that the proposed change doenst' broach the age limit.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2015
    Posts
    4
    Just to clarify in case it makes a difference to your reply:

    This app will enable an advisor of mortgages to ask a potential borrower some questions and then return a list of lenders that would potentially lend. For example:

    How old are you?
    What is your salary?
    Have you ever had any late payments on a loan agreement?
    more Q&As

    I'll then return a list of lenders that are potentially still willing to lend based on these answers and omit those that are not.

    Does that change your reply?

    Querying the data itself should be fairly straight forwards as you said.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by jheg View Post
    Does that change your reply?
    No
    because its your model for your business requirements you need to implement your solution
    I would expect an financial institution to have several mortgage / loan offerings
    so i would expect the rules affecting a specific loan offering to be defined within that entity, not at the bank level

    The precise business rule is down to you.

    is it they can't have a mortgage on or after that age, or the term has to change to reflect that upper limit
    are there other elements that effect the term f the mortgage (ferinstance some lenders will lend for a shorter period if they deem the property to be higher risk)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2015
    Posts
    4
    When you say model are you referring to Rails models?

    Also a financial institution does have many loan offerings but that is not the concern of this app. The app is only concerned with providing a list of lenders who will lend and the rules that govern this apply equally to all loan types the app is concerned with. Therefore there is not a specific loan offering and the database only needs to store values against each lenders criteria field.

    LanderA.max_age = 75
    LenderB.max_age = 70
    LenderC.max_age = 70
    etc

    LenderA.CCJs_in_last_12 = 0
    LenderA.CCJs_in_last_24 = 1
    etc

    Sorry if I am not being clear or not understanding your reply - I am very new to programming and even newer to databases. At this moment I am soley interested in where to store the data - in separate tables or the same table? If separate tables what sort of association would this require?

    There may be edge cases that trigger other criterium but that is something that I will add in time if needed. For now I will return all lenders will lend and all lenders that might lend.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    OK so take a step back from the trenches
    what do you think the CCJ rule applies to? what is it an entity / attribute of
    like wise what do you think the upper age limit applies to? what is it an entity / attribute of

    number of CCJ's in x months is, I'd argue the wrong way round... I'd suggest they should be in a table, individulay identified. why individulay, well a person coudl apply for a mortgage. it gets turned down because that indiviuals CCJ record is outside the limit
    two months later a lender reduces the CCJ cut off, and or one (or more of the CCJ's lapses). on your model you have no visibility of that. you don't know when a CCJ lapses. In your model you would need to re enter all the data next time the same applicant applies for a mortgage Yes you still have to ask if any more CCJ's have been triggered since the last application, but why for pre -known CCJ's
    Strictly speaking a CCJ is on file for a minimum of one month or a maximum of 6 months.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Feb 2015
    Posts
    4
    All rules apply to a lender object. So it should be an attribute of a Lender instance?

    With regard to the specific rules surrounding a CCJ I wouldn't worry about that - it's not important to this app and will only I feel confuse the matter further.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •