Blog 9 – Database changes

This is a quick blog to show some changes that I made to the database design. These were changes that I realized needed to be made as I was writing blog 8. Below is the previous design;

28-09-database-design

And this is the new design;

10-10 Database design v2.1.PNG

Changes;

  • I changed course_class to course_timeframe. I found that the course_class name did not best represent the entity. Course_timeframe was a better choice as this entity determines the when the course starts and when it ends, like a timeframe. Changing the name of the entity also meant that I needed to change the ID in the table.
  • In the class entity I have removed the class date attribute and changed the class start and end time from a time attribute to a datetime attribute. This is because before I was thinking about a class being just one day but sometimes they are trips or classes continued over a number of days so this means that there is a start date and an end date for the class
  • The attendance entity has now become more useful. Before I had added it as it was needed but I didn’t know how to implement it. Now it identifies a person with a class and also allows for the teacher to modify the student’s attendance record if needed. If modified then the it is marked as in the modified attribute. This will be used when checking and creating the attendance record.

There will need to be a bit more refinement as the website as developed. I am trying to create a model that I think will work for this project without fully implementing it. Therefore there will be some considerations in the database design that I have missed.

EDIT;

More changes since;

12-10 Database v2.2.PNG

When coming up with some of the procedures for the website I realized that having the Person (Teacher) linked to a course_timeframe through the Course entity meant that only the course could be only run by one teacher at once. If one course was being twice at the same time by two different teachers there was no way to differentiate the two. That is why I added Person_Course_TF. I know that it is repeating data effectively due to Course_Roll being identical. But Course roll is to determine the students on the course and Person _Course_TF is to identify the teachers teaching the course. This means that one or more teachers and teach the course and there can be more than one course running at once. It also made writing the procedures easier.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s