Iam creating an application which is being used to send reminders. Here is the requirements.
- We have set of Students.
- Every student has a
Student Pass, this need to be renewed in every month.
Student Passhas Pass ID, monetary value, date of expiry, etc.
- Before 7 days of
Student Passexpiry, we have to send notifications to the system admin.
- We have to monitor the
Student Pass Historyof every student as well. Which means how many notifications were given to the admin, what is the date the pass got expired and so on.
Now we need to create tables for this. We thought of creating a table called
StudentPass and maintaining the
student pass history and
current student pass in the same table. It is like below
However maintaining the
current student pass data and
student pass history seems to be
mixed up work because we have to send notification for the expiration of
current student pass. So we decided to divide this into 2 tables. It is like below, the
current student pass data will be maintained in
StudentPass table and the history data will be maintained in
With your experience in database, please let me know which method is better. In case you need more information about why we divided the data into 2 tables, that is because our notification system can simply identify that it should send notification only for the items in
StudentPass table, We do not have to do DB comparison to find out which data is the latest and current, we have history and current data separated and it will be easy to generate reports. Your advice is highly appreciated.