TABLE STRUCTURE OF A HIGH BASIC FEES COLLECTION SYSTEM

Question

Hi guys am new to Ms access, I am try building school database fees module using Microsoft access 2013. i want the to be able create and session{ year and term} to pave way for payment every term or semester or term. i am so stuck somewhere.

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

Answer 1

Basically this would be a ternary relationship type between payees, academic years and terms, or possibly a quaternary relationship type if the database covers multiple schools.  The former would require tables for the three entity types.  Payments represent the relationship type between the three referenced entity types, and would thus be modelled by a table which resolves the relationship type into three unary one-to-many relationship types, with the payment amount as a non-key column in the table.

However, this simple model is not adequate as there is a relationship type between academic years, terms and fees, i.e. a table of the fees payable for each term per academic year.  Consequently the payments table would need to reference this table rather than the academic years and terms tables individually.  The foreign key academic years and term columns in the payments table would thus be a composite foreign key referencing the composite primary key of the table modelling the relationship type between academic years, terms and fees.

If the database covers multiple schools, the referenced relationship type would be between schools, academic years, terms and fees of course.  The would also be a relationship type between schools and payees, again referenced by a composite foreign key in the payments table.

You might like to take a look at Relationships.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

This little demo file illustrates how relationships are built up across a database to achieve the final model.  It uses a simple medical prescriptions database as its example.

As you are relatively new to Access you might also like to take a look at the DatabaseBasics demo in the same OneDrive folder.  As its name suggests, this provides an introduction to some of the basic principles and methodologies employed in the design of a relational database application.

---

Ken Sheridan, Stafford, England