Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    168

    flexible finance table design

    Hi.

    I have a student's questionnaire that asks the student to input many details about their finance. The questionnaire is divided into several parts where it asks for personal information, addresses, etc.

    I have divided the financial part into 5 categories at present. These are my tables:

    FinanceEntity
    -------------
    - financeEntityID (PK)
    - financeEntityName
    - financeEntityPKName

    StudentFinance
    ---------------
    - financeID (PK)
    - financeEntityID (FK)
    - studentID (FK)
    - entityID (FK)

    In my financeEntity table, I will have table names that point to each of these 5 financial sections in the financeEntityName field like:

    financeEntityID financeEntityName financeEntityPKName
    1-------------- PaymentPlan --------paymentPlanID
    2-------------- PaymentMethod----- paymentMethodID
    3-------------- CreditCardAccount-- ccID
    4-------------- DebitOrderAccount-- debitID
    5-------------- Deposit--------------depositID

    Then everything is linked up in the StudentFinance with a studentID. Is that a good practice? Thanks.
    Last edited by kpeeroo; 10-26-09 at 22:51.

  2. #2
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    I have read this several times but I don't understand. In the top section you define the FinanceEntity table as having two columns, but in the last section you show three column. And you seem to have a lot more IDs than tables.

    Could you clarify please?

  3. #3
    Join Date
    Jul 2009
    Posts
    168
    Thanks for your reply Mark. Good to hear from you.

    Sorry for that ambiguity, I forgot to put the column name, which I just modified. I added that column, financeEntityPKName, to represent the PK of the table whose name is in column financeEntity. Therefore, I have presently 5 tables that hold financial data and they are:

    - PaymentPlan
    - PaymentMethod
    - etc

    These are tables and financeEntityPKName is their respective PKs. I don't know if this is what is called YAGNI, but as we are dealing with the finance section we have to make a flexible design because the company might add anything unpredictably. So if they add another financial entity say 'ecommerce payment', I will add a new table holding 'ecommerce payment' information and put this entity in my FinanceEntity table.
    Last edited by kpeeroo; 10-26-09 at 23:04.

  4. #4
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    Ah, I see it now. So your StudentFinance table holds

    - a primary key
    - the foreign key to the student
    - the foreign key to the FinanceEntity table that defines the financing type
    - a key to one of many tables that define the actual financing

    You won't be able to put a foreign key constraint on the last key since it goes to many tables. I would recommend defining StudentFinance as:

    StudentFinance
    ---------------
    - financeID (PK)
    - financeEntityID (FK)
    - studentID (FK)

    Then putting the financeID as a foreign key in the various PaymentPlan, PaymentMethod, etc tables. That will let you have a true foreign key constraint (back to the StudentFinance table), yet you will be able to join to the proper payment method by looking at the financeEntityID (the StudentFinance table will say they paid with a credit card so you know to join to the credit card table).

  5. #5
    Join Date
    Jul 2009
    Posts
    168
    Yes you got the idea correct. But will putting the financeID as FK to the other tables work? My PaymentPlan table goes:

    PaymentPlan
    ------------
    - paymentPlanID (PK)
    - name
    - installments

    PaymentMethod
    ---------------
    - paymentMethodID (PK)
    - name

    examples of the tables:

    PaymentPlan
    ------------
    paymentPlanID---------name---------installments
    1----------------------Cash------------0
    2----------------------Basic Fee--------2
    3----------------------Easy Fee--------5
    4----------------------flexible fee------9

    PaymentMethod
    ---------------
    paymentMethodID------------name
    1----------------------------direct deposit
    2----------------------------direct payment
    3----------------------------debit order
    4----------------------------credit card

    So I cannot put financeID as FK in these tables right?

  6. #6
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    You are correct. Without examples of what PaymentPlan and PaymentMethod looked like I was assuming something else. You would be best off keeping the design you started with. It doesn't let you create a foreign key constraint, but it does accurately contain the data.

  7. #7
    Join Date
    Jul 2009
    Posts
    168
    I like the FK constraint you proposed though and am thinking if there are ways to include this. Any idea or should i just move on like this?

  8. #8
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    I can't think of a cleaner way of handling it than what you have. Somebody else might have a better idea, but personally I would go ahead with what you have. You can't always wait for the perfect answer.

  9. #9
    Join Date
    Jul 2009
    Posts
    168
    Cheers mark. This is what I just did. Was just on the design thing. Thanks for your advice.

Posting Permissions

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