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.

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?

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.

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).

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?

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.

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?

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.

Cheers mark. This is what I just did. Was just on the design thing. Thanks for your advice.