Fee Management System for School

Question

hello ,

i need to design a database for the fee management for school. which any student  can pay at any time i mean he can pay amount as monthly basis or annually. here there will be fee structure for each class depending on fee structure the student can pay the amount.

here i have designed the two table one for Fee structure and one for Feepayment

FeeStructure:

CREATE TABLE [dbo].[student_fee_structure](
[fee_structure_id] [bigint] IDENTITY(1,1) NOT NULL,
[Class_id] [int] NOT NULL,
[fee_name] [varchar](50) NOT NULL,
[fee_collection_type] [varchar](50) NOT NULL,
[fee_amount] [money] NOT NULL,
[fee_discount] [money] NOT NULL,
[fee_description] [varchar](50) NOT NULL,
[academic_year] [varchar](50) NOT NULL,
[medium] [varchar](50) NOT NULL,
[fee_total_amount] [money] NOT NULL,
[last_day_pay] [varchar](50) NOT NULL,
[fine_amount_per_day] [varchar](50) NOT NULL,
[status] [varchar](50) NULL

FeePayment:

CREATE TABLE [dbo].[Student_fee_payment](
[fee_payment_id] [bigint] IDENTITY(1,1) NOT NULL,
[student_id] [bigint] NOT NULL,
[total_amount] [money] NOT NULL,
[discount] [money] NOT NULL,
[paid_amount] [money] NOT NULL,
[due_amount] [money] NOT NULL

here in feepayment all student payment details will be stored here. .wherever student pays the amount we need to reduce the amount from the total amount  and need to show it as due amount.when the same student comes to pay the fee Total amount for next time should show the last time due amount  to be paid by the student.

I am new to the Sql server doe i am in a write way for designing the Database.any suggestions will be appreciated .

Thanks In Advance

Answer 1

Hi ABDUL,

I must start with saying that I do not think that it is a good idea to design your database based on the forum. A database architecture is something much more complex than a simple question. To get the right architecture you must have full understanding of the business logic, full understanding on needs, agendas, and goals, but much more, you must have full understanding of any related system (directly or indirectly related), and more. Personally I will not give advice regarding what fit your specific need :-(

With that said, I can offer some points that can help you get the decision :-)

1. As first step, I recommend to think about the entities that will take part in the application.

For example let's parse your short description:

>> i need to design a database for the fee management for school. which any student can pay at any time i mean he can pay amount as monthly basis or annually. here there will be fee structure for each class depending on fee structure the student can pay the amount.

you mentioned "school", "student", "fee", and "class". These might be the basic entities logic of the database, but don't be hasty!

2. Next, go deeper and make sure that you understand the way people will use these entities (Think about services or modules or APIs in the application). Try to imagine your system in action. draw some of the basic GUI interface. I HIGHLY recommend to look for more information regarding Service-oriented architecture. It is one of the simplest development architecture (not the best maybe but great as starting point for any developer), "and the way of thinking" fit for database design as well.

For example, is your "fee" made by external application or does someone need to do it at the same system. Who is that "someone" ?!? Is this mean that anyone can use the system and do whatever he want to do? Probably not :-). It is clear that you will need some type of "users" module (which mean that this is part of the database architecture). Well... as you see, now you need to think about the users module. will you have an admin to the application? probably you will. You will probably need Full users system. Check this new article in the TechNet WIKI, it is perfect as starting point:  ASP.NET MVC 5 Security And Creating User Role. 

3. Now go deeper to each entity (at this point you should already have a list of basic entities in your system)... you need to think about the relations between the entities.

Continue the example above regarding users, I think it is already clear that you have several rule for "admins", but should "students" be entities or just a rules, or maybe both?!? Who are the users that actually use the system? are students allowed to use the system (post payment information directly) or only the admin insert the data? In first glance I would guess that student have a lot of information that is not part of the users information, so this probably another type of entity. If students are also users then think about OOP a bit... any student is first of all user. These tables are related with one-to-one relation.

What not to do?!? This about your table name "Student_fee_payment". This name limited you to fees made by students... why?!? Tomorrow you will need to add fees by teachers for example (payment to workers' committee, tours, activities).... will you create another table named Teachers_fee_payment?!? You should keep your design flexible as possible!

* Usually the table name for the entity is in plural, like Payments, and not Payment. The table include multiple entities from the same type.

Another example: If you decided to have a column named [Class_id] in a table, and this is not the Classes table, then this should be related to the Class entity (the Classes table for example) using foreign key. Your table structure implies on other tables that you are missing in the system. 

* I think that the name FeeStructure is really no good. a fee these not have a structure. You might think about PaymentsTypes table and Payments. I recommend to search the internet for: online payment system database design.  You should find some good examples. It is not matter if you pay for disk in online store or if you pay for class... the basic design is the same and in most cases it is best to leave it fit for all cases (flexibility / Code-Reuse). 

As you can see there is a lot to think about and this is not even the starting point :-)

In summary

using only simple FeeStructure and FeePayment tables is HIGHLY NOT recommended by me, and does not give you the answer. Moreover the structure of these tables and their name does not fit in my opinion.

* If this is interesting I can continue... please inform me if I am helping you or just confusing you.
I just don't want to wast time, and it took me long time to write this :-)
In this case, please try your best to re-design the tables that you need. start with list of entities (post this list) and work according to the above comments

---

Ronen Ariely  [Personal Site]    [Blog]    [Facebook]    [Linkedin]

Answer 2

While I agree with the sentiments here, here's something to get you thinking:

DECLARE @fees TABLE (feeID INT, feeName NVARCHAR(50), feeAmount MONEY, feeDescription NVARCHAR(MAX))
DECLARE @studentFees TABLE (studentID INT, feeID INT, feeSeq INT, classID INT, discountApplied MONEY, billDate DATE, dueDate DATE)
DECLARE @students TABLE (studentID INT, studentFirstName NVARCHAR(50), studentLastName NVARCHAR(50))
DECLARE @payments TABLE (paymentID INT, studentID INT, feeID INT, feeSeq INT, paidAmount MONEY, paidDate DATE)

INSERT INTO @fees (feeID, feeName, feeAmount, feeDescription ) VALUES  
(1, 'Math Tutition', 1000, 'Tution for Math'),
(2, 'Science Tutition', 1000, 'Tution for Science'),
(3, 'English Tutition', 1000, 'Tution for English')

INSERT INTO @students (studentID, studentFirstName, studentLastName ) VALUES 
(1, 'John', 'Smith'),
(2, 'Smith', 'Wicks')

INSERT INTO @studentFees (studentID, feeID, feeSeq, classID, discountApplied, billDate, dueDate ) VALUES  
(1, 1, 1, 1, 200, '2015-09-01', '2015-12-31'),
(1, 1, 2, 1, 200, '2016-01-01', '2016-03-31'),
(1, 1, 3, 1, 200, '2016-04-01', '2016-06-30'),
(1, 2, 1, 2,   0, '2015-09-01', '2015-12-31'),
(1, 2, 2, 2,   0, '2016-01-01', '2015-03-31'),
(1, 2, 3, 2,   0, '2016-04-01', '2015-06-30')

INSERT INTO @payments (paymentID, studentID, feeID, feeSeq, paidAmount, paidDate ) VALUES  
(1, 1, 1, 1, 400, '2015-09-05'),
(2, 1, 1, 1, 400, '2015-10-05'),
(3, 1, 2, 1, 400, '2015-11-05')

;WITH paymentsMade AS (
SELECT studentID, feeID, feeSeq, SUM(paidAmount) AS totalPayments, MAX(paidDate) AS lastPaidDate
  FROM @payments p
 GROUP BY studentID, feeID, p.feeSeq
)

SELECT s.studentID, s.studentFirstName, s.studentLastName, SUM(f.feeAmount-sf.discountApplied) AS TotalFees, SUM((f.feeAmount-sf.discountApplied)-COALESCE(p.totalPayments,0)) AS totalOwed, SUM(COALESCE(p.totalPayments,0)) AS paymentsMade,
       SUM(CASE WHEN (f.feeAmount-sf.discountApplied)-COALESCE(p.totalPayments,0) > 0 AND dueDate <= CURRENT_TIMESTAMP THEN (f.feeAmount-sf.discountApplied)-COALESCE(p.totalPayments,0) ELSE 0 END) AS pastDue
  FROM @students s
    INNER JOIN @studentFees sf
	  ON s.studentID = sf.studentID
	INNER JOIN @fees f
	  ON sf.feeID = f.feeID
    LEFT OUTER JOIN paymentsMade p
	  ON sf.studentID = p.studentID
	  AND sf.feeID = p.feeID
	  AND sf.feeSeq = p.feeSeq
 GROUP BY s.studentID, s.studentFirstName, s.studentLastName

---

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
Really enjoyed it? See my profile!
My Tech Net Articles.