Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2012
    Posts
    11

    Smile Unanswered: My Second Database Design

    I have written a database driven C#.net form application. This application can calculate loans, and create new loan accounts for new customers. It can also search the database and return filtered results. My database design consists of seven tables, there is a many-to-many relationship between CustomerInformation table, Address, LoanInformation, and EmployerInformation tables. There is also a one-to-one relationship between Address and Phone tables. In addition, there is a many-to-many relationship between LoanInformation and PaymentInformation. Finally, there is a many-to-many relationship between EmployerInformation and CreditInformation. My goal and objective is to have this design up to the 3NF, and I sure could use some feedback.

    Thanks
    Attached Thumbnails Attached Thumbnails Bank3.jpg  

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    1. The LoanDate attribute should be on LoanInformation, not on CustomerInformation
    2. For what reason did you created the PaymentInformation relation? All its attributes may reside in LoanInformation
    3. Same question with Address and Phone relations
    4. In EmployerInformation relation, Income, yearsEmployed and Occupation attributes are partial dependencies. So their place is on CustomerInformation relation
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Jan 2012
    Posts
    11

    Updated

    I have made some modification based on your recommendations. I have combined both phone, and address table into new contactInformation table. I have also combined loan and payment information into the original table LoanInformation. Finally, I have moved employer information into customer information, while retaining the CreditInformation table.
    Attached Thumbnails Attached Thumbnails Bank4.jpg  

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Some comments on your data model.

    1) names: There is no need to use the word "Information" in the name of each and every table name.

    2) Why did you call the table Customer (I assume you will omit "Information" in all table names) and the PK AccountId? Make sure those names are consistent. In this case it hints in the direction of another problem: how many accounts can one customer have? Shouldn't there be two tables then?

    3) There is no need to use two columns for the PK of the Loan, Contact and Loan tables. Just one column, like LoanId is enough. Let CustomerId/AccountId be a FK, but don't make it part of the PK.

    4) Why do you store the Contact data in another table? Why not directly into the Customer table? You could store the first name and the last name in two different tables too, but why would you do that?
    If you are interested in keeping historical data, like from 1990/01/01 he lived in New York, StreetA, ... then from 1993/05/01 he lived in Washington, SteetB, ... you can keep your current table layout, but then you need to add a Start_Date column (and get rid of columns that are not related to someone's living location, like cellular nr, e-mail address, ...).

    5) The Credit table has a column YearsOfCredit. This value should not be stored, as it can be calculated at run time by using the current date and the start day of his credit approval. So dump the YearsOfCredit and add a Start_date. It's the same logic as storing a person's age versus storing her/his date of birth.

    It is very hard to give you any advice on this data model, without any business logic. We can guess the rules that should be supported by the data model, but we could be far off.

    Like the example with the Contact table.
    - If you want to store historical address information, you could add a start_date column and throw out the columns that are not related to someone's address location (like e-mail, work phone number, ...).
    - If you don't want/need to store historical data, simply move all the columns from Contact to Customer and drop the Contact table.
    You see, without us knowing what business rules you have to deal with, there is no way we can really help you.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2012
    Posts
    11
    Business Rules
    1. 20% down payment is required
    2. This database is for a program that was just a mortgage calculator, which I decided to extend and store each users calculations in a database.
    3. In order to complete new account form you must first run account calculator to calculate monthly payment and total interest.
    4. In order to create a new account you must search the database to see if account already exist.

    Thanks
    Attached Thumbnails Attached Thumbnails Bank5.jpg  

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by samijohn View Post
    4. In order to create a new account you must search the database to see if account already exist.
    that's not a business rule, that is just one way of achieving a business objective

    the business rule is realistically better stated as "there shall be no duplicate accounts"

    searching first before inserting is only one way of achieving that -- there are others
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2012
    Posts
    11

    Smile

    My third database design is a lookup tables called CreditScore and MortgageInterestRate. There is one-to-many relationship between the two tables. I am using a stored procedure to get the credit status and interest rate from these tables. I would greatly appreciate any feedback anyone can give me

    Thanks
    Attached Thumbnails Attached Thumbnails CreditScore.jpg  

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by samijohn View Post
    I would greatly appreciate any feedback anyone can give me
    could you show some sample data for each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2012
    Posts
    11
    MortgageInterestRate table

    CreditScoreID CreditScore MortgageInterestRateID
    100 760 Excellent
    101 761 Excellent
    102 762 Excellent
    . . .
    . . .
    259 501 Very Poor
    260 500 Very Poor

    CreditScore Table

    MortgageInterestRateID InterestRate

    Excellent 5.78
    Very Good 6.002
    Good 6.286
    Fair 7.096
    Poor 8.583
    Very Poor 9.499

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it is as i expected

    well done

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2012
    Posts
    11

    Smile

    Hi, I have one last question to ask, and as you can see my MortgageBank table has grown that is because I did not have all the requirements upfront. In addition, i have one new table, called AssetAndDebt but I am thinking how to integrate this into my tables above. I first added those columns to my LoanInformation table, but things just got pretty confusing.


    Any Suggestions
    Thanks
    Attached Thumbnails Attached Thumbnails Bank6.jpg  

Posting Permissions

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