Help on the table structure of a secondary school database

[Wabamdo]

Hi guys,I am developing a secondary school database fees module using Microsoft access 2013.The school contains classes from Form 1 to Form 4.Some students of the school are boarders while others are day scholars.These two student's categories pay different school fees amount.Fees is charged to the students twice per year and different classes i.e form 1 to Form 4 pay different fees amount.Please me on how i will better this table structure .

Thank you in Advance.Any help will be appreciated very very much

[ranman256]

I see these tables
tFees - different charges , BOARDER or DAY
tClass - reading 101, rithmatic 201
tClassSemi -classes from tClass used in a given Semester /yr
tStudents - name, addr, zip
tStudentClass - what class student takes / semester/year, ClassSemiID

tStudentClass fields:
StudentID
ClassSemiID
Grade

[Micron]

Another approach:

tblClass .Type(text) .Desc(text)
[e.g. Form1 / description]

tblStudent .ID(unique number); Type(tblStntType.ID); .LName(text); FName(text); .Address(text); .City(text) etc.
[e.g. 123; 1; Wabamdo; Robert; 123 Main St.; etc;

tblStdntType .ID(unique number); .Type(text); .Desc(txt)
[e.g. 1; Boarder; resides on campus]

tblPayments StdntID(from tblStudent); Semester(number/txt); Type(tblFees.ID); .AmtPaid(number)
[e.g. 123; Spring; 1; 100 (represents Name,semester,fee type from tblFees and amount paid/owing)
see note below on this setup


tblFees ID(unique number); .Type(text) .Amt(number)
[e.g. 1;Boarder;100...2;Form1;300

I would not have a balance field in tblPayments as it is not good practice to use stored calculated values.
Also, relating ID's between fees and payments presents a caveat. You would need to understand that changing the fee would result in historical payment amounts being changed because the amount is being referenced. To avoid this, you could use a composite primary key of tblFees.ID and tblFees.Type, allowing many fees for a type because they'd all have a unique id. Or you could use tblFees as a record source for a combobox to pick a fee when filling out a form and avoid any such relational issue. I would do the latter.

Sometimes I will use Excel to plan database table structure. It helps to plan the names of tables and fields, relationships, data types, and if needed, field sizes. Helps prevent confilcts such as text/number and integer/long too.