Hi,

I have 2 tables for storing payment activities of students as thus:

StudentPayment
----------------
- paymentID
- studentID (FK)
- amountPaid
- date
- feesID (FK)
- feesRemaining

OwedFees
----------
- feesID
- tokenID (FK)
- fees
- date
- studentID (FK)

My question is do I need studentID as FK in both tables when feesID is already FK in StudentPayment table? Thanks

What dependencies are supposed to be satisfied by these tables? Ask yourself what are the determinants of those attributes.

At the moment, just a few other tables may depend on these 2 tables but I am thinking what if later, further implementation of other tables need to be done that may or may not depend on these tables.

What I mean by dependencies are functional dependencies or join dependencies between attributes in the tables. That's what should determine which attribute belongs in which table. If you don't know what I'm talking about then look up Normalization in any good book database design. I'm assuming this is your homework which is why I didn't answer directly.

bro this is no homework lolz! but a software that am designing using .NET framework and since am new to database design i want to make sure i don't mess up things because it would be storing important student information for a business school. things like credit card information, academic details, etc. so rest assured you can go easy on the explanation not fearing that i will score high marks in assignments )!

You don't need the StudentID in the StudentPayment table because you can get the StudentID by following the FeesID to the OwedFees table. This assumes the student won't be making any payments before the fees have been created.

You also don't need the feesRemaining column since you can always add up the payments to see if they match the fee amount (the amount remaining is considered derived data since you can determine it from existing information).

Oh MarkATrombley. Respect man. This is the type of answer that is very precise and straight to the point and can help people very much in learning new things. I was trying to dig up on 'dependencies' when your reply came up. This is what I had in mind, what you just said, so to be sure of it I asked that question before proceeding with the design. Cheers man, appreciate it very much

Mark's answer is an assumption (perhaps a good one) based on a list of column names. There's no way we have enough information here to give you a definite answer. That's why I suggested you study something about the topic first so that you will be able to answer the question for yourself. You can't expect to learn anything if you just take at face value everything said in an online forum. Think carefully before you base your professional work on such shaky foundations.

Following the discussion:

I have other tables in the finance section like i mentioned the credit card table:
So again when I read about normalization, they seem to narrow down or break up big tables into smaller sections or atomic parts.

Suppose for the credit card table i came up with this structure:

CreditCard
----------
- ccID (PK)
- cardTypeID (FK)
- accountNumber
- cvc
- expiryDate
- studentID
- ccBudgetAccountID (FK)

Now, I am thinking what if later the bank wants to add other features, so this table structure wont hold and would have to be modified which according to normalization rules is not a good thing. So what is the best structure? Will this one do? :

CreditCard
----------
- ccID (PK)
- cardDetailsID (FK)

CardDetails
-----------
- cardDetailsID (PK)
- name

CreditCardAccount
------------------
- ccAccountID (PK)
- cardDetailsID (FK)
- cardDetailValue
- studentID (FK)

There is no rule against modifying tables. It's normal for a database schema to evolve over time as business needs change. Evolutionary development is a good thing and you shouldn't be afraid of it.

I doubt it but then I don't really understand how it relates to what went before. In all your sketches of table structures you seem to have indicated foreign keys but not candidate keys, which makes it difficult to understand you. What is/are the candidate key(s) of the CreditCard table in this example?

I thought it would be obvious because I was focusing on FK. I know development is a good thing but the ripples it causes should be minimized to the least. So what I'm looking is a structure that adapts to changes easily. This is the rule I am specifying which according to normalization should be applied. My PK in all these tables are the first row in each table for CreditCard table its thus ccID.

Like in the structure I thought, even if we add as many other attributes over time, it does not need much change except if we have major changes like a parent to many child relationship. This is just my opinion of what little I've gone through database theory. So I want your ideas on it.

In your example each card (ccID) can only have ONE CardDetailsID associated with it. I assume the CreditCardAccount table is supposed to contain both CVC and ExpiryDate attributes as rows but that looks like it's impossible in your model.

You could change that by putting ccID into the CreditCardAccount table but then you still only have one attribute (cardDetailValue) to store both a date and a number. That means you won't be able to use the correct data types. Maybe you were planning to use a string or binary for this data but that has obvious disadvantages. You won't easily be able to add constraints and other logic if you don't use the right types for things.

This is a very poor trade off because you would be making a model much less effective for the attributes you already know about and for the future ones you don't know about yet as well. Classic example of where you need to apply some YAGNI thinking.

Cool dportas. I did not consider the types for the 'cardDetailValue' so you are right there, it will be a pain. This YAGNI thing does indeed waste time and energy. Thanks for pointing this out.

So, if I go with my first table of the CreditCard, how do we implement changes later if we need to add another attribute for instance? just modify the table and allow NULLs where this attribute is not available for earlier records?

I wonder if it's YAGNI as well :

To sum up the finance table I have categorized and divided the form, which students fill up, to end up with a structure as thus:

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

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

e.g of FinanceEntity table:
Basically this is the 5 sections that I divided the form into and each section relates to a table. Hence:
- Payment Plan ----> PaymentPlan table
- Paymeny Method ----> PaymentMethod table
- etc

Then the student's details are summed up in the StudentFinance table. What are your thoughts and opinions?