Database Design
 |
Final project paper (PDF, 490KB) The semester project for the course "Datenbanksysteme" (winter term 2008/09) was to design a database for a (fictitious) customer.
In my case this customer was a private school offering courses dealing with New Media and associated concepts and (software) tools. The database should facilitate the administration of courses, participants, lecturers and all associated transactions like billing etc.
The image shows the final DB schema design as an Entity-Relationship diagram. The extremely structured nature of database design is very appealing to me and I especially enjoyed developing a representation for business transactions.
Special Problem: Business Transactions
Finding an appropriate DB design for information like personal data, street adresses and similar things is relatively straightforward. The structure of this information and its relations to other information is static and known at the time of design.
But not all the information that is critical for a business is like this. One special kind of information that my design must cope with is business transactions ("Geschäftsvorfälle"). Simply put this is information about everything that happened at some point in time. This might include information about bills sent, received payments or bookings or even taking note of a customer pone call.
The challenge lies in the fact that these "informational items" are not fully known at the time of design, so it was not possible to build a schema that directly reflects them. A thorough analysis of the business processes might yield a complete set of such items, but they are almost certain to change in the course of time. So it must be possible to add or change arbitrary business transaction types easily, that is, without the need to change the database structure.
The solution was to create and store definitions of business transaction types (see diagram table "GVTYP") and let the actual transaction records (see diagram table "GESCHAEFTSVORFALL") reference their respective type. So both the actual transactions and their structural definition are stored as table data instead of being reflected in the table design.
If you are interested in the details, please have a look at the final project paper and download the raw SQL dump of the database if you want to experiment with it for yourself.
Tools Used
 |
- PostgreSQL and pgAdmin
Realizing the database design in an actual DBMS was not a requirement for this project, but having a real database populated with some test data to play with made the development of views and standard SQL queries much easier. Because views were a requirement I chose PostgreSQL. The excellent graphical administration and development interface pgAdmin provided a very comfortable way to realize the database design, enter test data and develop views and queries.
- All grahics were created using the open source vector drawing tool Dia.
Dia is especially suited for this type of graphics because it ships with a library of standard symbols from many different application areas an the possibility to really connect arrows to other elements so that they get updated f the position of the elements they are connected to changes.
- The paper was typeset using LaTeX and Kile as text editor.
BibTex bibliography references were managed using JabRef that made it very easy and convenient to handle all information about the sources used in this paper. Highly recommended!