Designing the Calendar-Holiday Database Application
This is part one of a three part series. The second article will cover building and populating the Calendar Holiday database. The third article will cover some approaches to developing an application to use the calendar information.
The factors (requirements) which drive user interface design should drive database design. A well designed database makes the developer's job much easier, especially when using ORM (Object-Relational Mapping) tools such as Entity Framework or Hibernate, or rapid application development tools such as Iron Speed Designer.
The Calendar project is a learning and exploring project based on building a clone, or near-clone, of an existing application. If you like, you can build the database from the supplied scripts. We will discuss the decisions that went into the design, but a version of it already works, so our course is known.
Most development projects are creating something new with uncertain requirements. As I tell my clients, every project is a voyage into the unknown. Novel projects require constant interaction with the end users to stay on a constantly changing course. This is the where rapid prototyping and Agile development can help keep a project on a productive course.
I have been reading a "discussion" on Slashdot about how Agile development strategies leave developers without enough guidance. This clearly misses the entire point. Development strategies are tools. Direction should come from the end users through the analysts, project managers, and designers. I suppose I should feel sorry for developers working under a lack of leadership, or incompetent leadership, yet I agree with an old saying that "It is a poor workman who blames his tools."
In a way, this project is easy. The modules of the Calendar project are like plumbing. The underlying requirements are pretty straight forward. We can get creative putting different front ends on our project. I guess that would be equivalent to different bathroom and kitchen fixtures, but the pipes stay the same.
- SQL Server 2008+ and SQL Server Management Studio (SSMS)
- ASP.NET 4.0+, C#, and Visual Studio 2010+
This application provides a Web calendar which presents holidays. The dates are based on the Gregorian/civil calendar used in business in most countries. The default data supplied as part of this exercise includes both fixed holidays and movable holidays.
Fixed holidays are on the same date every year. An example is Christmas, which is always on December 25.
Movable holidays are holidays that move from year to year. Most of these holidays are religious holidays based on lunar calendars, and include Christian Easter, Jewish Passover, and Muslim Mawlid al-Nabi.
Since these holidays are saved in database tables, they can be added to, removed, moved, or updated. For example, if this application is used by a business, company holidays, paydays, meeting dates, etc. can be added.
I intend for this project to be both a practical resource for developers, and an intermediate level training exercise for developers who would like to know more about database design, and DBAs who would like to know more about how database design affects developers.
This project grew out of a design for a hotel registration application. The project was not funded, but the Calendar application grew out of the calendaring portion of the hotel project. The practical result of this pedigree is that it would not be too difficult to add (again) event calendaring to this design.
Calendars are used in many applications. This calendar is a subset of the calendar of a hotel registration system and can easily be extended for more functionality. Additional uses will be left to the esteemed reader.
The dates in the Calendar table are based on the Gregorian/civil calendar used for business in most countries. The coverage provided by my script is 1-January-2009 to 6-June-2031.
I used the US convention that the week begins on Sunday. It would not be too difficult to change the beginning of the week to another day in the Calendar table.
Each database module provides a related set of functions to our application. The modular design allows each section to be built and tested independently. The modules and tables are described in detail in the next section.
- The Calendar module which consists of nine tables. The heart of this module is the Calendar table. The calendar table consists of a list of every calendar day for about twenty-one years, with some additional columns such as weekends and pre-computed day of the week, day of the month, day of the year, etc.
- The Person module which consists of five tables. The Person module is used to store data about people, which is used for personal holidays, such as birthdays, and it is also used to supply person information to the Account manager module.
- The Account manager module which supplies storage for authentication and authorization information. The account module also allows us to do simple auditing of changes to the database when data is added or modified in the tables.
The table maintenance module provides a form to create, edit, update, and delete records in each table that requires human interaction. Access to these forms is usually reserved to the system administrator and is used for fixing data problems.
A module to maintain the calendar information. These forms perform some of the same functions as the table maintenance forms, but in a more user friendly way. The maintenance forms can be the basis for these forms to reduce the amount of development required. Access to these forms is usually limited to administrative personnel.
- The public Calendar display and holiday search forms, possibly read-only.
- Detailed description of the Calendar-Holiday application will be another article.
- The Calendar database and application can be built as a standalone Web application. I can imagine that some of you will want to tackle the Calendar module first, in fact, this might be all of the project that you need.
First, a few words about the diagram. This Visio diagram shows the nine tables of the Calendar-Holiday module.
The lines represent relationships between the data in columns in different tables. The relationships will be created by scripts that are included with this documentation, and they are properly called Foreign Key Constraints.
The lines running off the screen to the left are connections to the other modules. The light grey lines connect to the Account table and provide the data for the CreatedByAccountID and ModifiedByAccountID columns in each table, which provide simple auditing.
The Foreign Key Constraints would prevent the Calendar module from functioning without the Account tables, but we can disable this restriction by not running the scripts that create their Foreign Key Constraints.
As you can see from the diagram, the Calendar table contains dates and information about dates. The holiday information is in other tables which are linked to the Calendar table by Foreign Key Constraints.
These tables hold the text names of the weeks and months. If you would like to change the change the names of the days, perhaps for a language other than English, change the names once in the DayOfWeek table, and you are done. Do the same for the months, if you like.
Bonus: Having the days of the week and the months as numbers in the Calendar table makes date arithmetic much easier.
Fixed holidays happen on the same day every year. Because these holidays don't change, only one entry is required. For example: Christmas Day on December 25, Cinco de Mayo en el Cinco de Mayo, or India's Independence Day on August 15 are each entered once in the database.
These holidays are usually fixed by other calendars and the translation is not to the same day each year. Our Calendar table covers 21 years, so each holiday's dates must be entered 21 times. One result is that the HolidayMovable table has a lot more entries than the HolidayFixed table. This could change if enough fixed holidays are added, such as birthdays or anniversaries.
The holiday types table allows assigning a group to holidays such as US Civil Holiday, Canadian Civil Holiday, Muslim Holiday, Christian Holiday, Korean Civil Holiday, etc.
Holiday categories would be groups of types, such as Civil Holidays, Religious Holidays, Bank Holidays, etc.
This section is required if you want to implement security by providing user authentication and authorization. Authentication is requiring the user to prove who he/she is. In this case, we require an account name and a password. This is a very basic level of authentication.
Authorization is giving the user access to selected functionality. Some users can have full access and others can be limited to partial access. This form of authorization can be managed by a user with the proper authorization.
If you do not want to protect your application, you can leave these tables out, but anything on the public Internet is at risk, even with some security.
The Account Table stores the basic authentication and authorization information. The name of the account, the date access becomes available, and the date access is terminated. The Password table stores users' passwords.
There is a field called Active that can be set to zero to immediately deactivate an account.
These days it is common to require a valid email address as an account name. The validity of the email address is tested by sending the new user an email to the address they used as an account name. The account is not validated until the user responds by clicking a link in that email. The hasValidReply field is used to record whether the user has responded.
The Pasword table is used to keep track of a user's passwords over time. This is usually done to prevent the reuse of old passwords. If you don't need this feature, you can eliminate this table and add a Password column to the Account table.
The AccountRole table stores the roles that are available for users, such as: Administrator, Manager, Super User, data entry operator, etc.
The AccountAccountRole table links this table to the AccountRole table. It creates a many-to-many link between Accounts and AccountRoles. Each user (Account) can have multiple roles, and each role can be assigned to many users.
The LoginAttempt table records every attempt at logging in. It is one way of detecting attempted break ins. If you don't want this level of security, just leave this table out of the database and/or don't write the code to record and monitor login attempts.
This module stores a basic collection of information about people who have accounts, or people who have holidays. The separate Name table allows for a person's name to change over time. The separate gender table allows for changing the names of the genders to accommodate a language other than English.
If you want to store more information it would be easy to add an address table and/or add a large comment field to the Person table.
If you do not want to store this much personal information, these tables could be eliminated and the person's name could be stored in a column in the Account table.
This table stores some personal information and links to more information including name, gender, and contact information.
This table stores gender names and identifying numbers in an internationally recognized format. If you changes this to a many-to-many relationship you could allow for a person's gender changing over time.
The separate Name table allows for a person's name to change over time.
The PersonContact table stores phone numbers, email addresses, and Web site URLs. The PersonContactType table stores the names of these types of contacts such as Personal Phone, Cell Phone, Personal Email, Business Email, and URL.
My (Conrad Muller's) work on this page is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 2.5 License.