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