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 Pass
has Pass ID, monetary value, date of expiry, etc. - Before 7 days of
Student Pass
expiry, we have to send notifications to the system admin. - We have to monitor the
Student Pass History
of 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 StudentPassHistory
.
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.
Best Answer
One thing missing from the sample tables above is
BeginDate
andEndDate
(or whatever you want to call them). Since you are tracking history, the dates are obviously valuable and can be used when querying older entries.Having a
StudentPass
and aStudentPassHistory
table does split out the information, but that is a mixed blessing, since the student's fullStudentPass
information is split across two different tables.You might consider implementing
StudentPass
like this:This way, the
BeginDate
andEndDate
clearly identify not only whether theStudentPass
is active, but could also preview for you the approachingEndDate
. Likewise, a futureBeginDate
could also support setting up aStudentPass
that will not be activated for a few more weeks.The
PassActive
is not strictly needed, but can be a useful shortcut for querying activeStudentPass
data without using dates. You just need a process to update thePassActive
column setting as defined to be in the inclusive range of theBeginDate
andEndDate
.Using a design like this keeps all the information in one table, which I assume will not overwhelm your query processing, so that you can simply filter the data according to your needs.
Of course, if you feel that the
StudentPassHistory
would server you better for some other needs, then make the choice to also create that table.