Database

Design

For the design of the database I used the program MySQL Workbench. The database model can be downloaded here as mwb-file, pdf-file or as png-file. I use the MySQL-engine InnoDB, so the used foreigh key constraints are vadidated automatically by the database system.

Tablel structure

Main Table

The most important table is the table member_entries. The information about every member is stored here. Most fields speak for themselves. There are on the other hand some field, whose meaning is not so obvious:

Field	Explanation
limit_access	This field can be used to hide some members from other members. When for example some members only want to give their data to the board, but not be visible to other members.
partner	When the club has a partner rate or when members should also edit the data of their partner, then the index of the partner is stored here.
company_information	For some clubs it is important that the members can see, where the fellow members work and what is their job at a certain company. This can be stored here.
user_group	The user rights in the the system are set in user groups. Every member is member of exactly ONE user group.
preferred_language	When there are different nationalities among the members, this field stored the language the member wants to be addressed. This entry can only be one of the values defined in the table languages.
membership_type	This is for clubs which have different membership types, e.g. with different membership fees.
membership_end	Usually the membership end when the club year ends. When this field is set to true, this member is still a member, but can for example be moved to the former members category at the end of the year.

Address data

The address data is stored in a separate table. This has the advantage that more than one address can be stored per member. Another advantage is that the same address data can be used for partners, so a change of the address is automatically done for both members. For the connection between address data and members there is a mapping table. For every member exactly ONE entry in this table must have the field main_address set true. This is the postal address which will be used for sending mail (as far as this is not done by email).

The field address_type which kind of address we are dealing with. This is defined by the administrator and is for example used for home address, study address, company address, etc.

Languages and translations

The table languages contains all languages which can be selected by the user, i.e. languages for which a translation is available in the system. The system searches the table with the translations for the translated string by using the module (module in which the text is used, e.g. the file name), language (language of the text block) and key (a string which is unique within a module).

Student unions

When the system is used for a student union, is makes sense to also store the field of study with every member. In the table fields_of_study all fields of study are defined. By using the table studies_student_map members can have more than one field of study, where starting and end year can be entered.

Committees

Most clubs have committees, like sports committee, party committee, etc., but also the board. For every committee is defined which functions are possible. Via the table committee_function_map is defined, which function is possible with which committee. A second table, committee_member_map, defines which member has which committee function.

Email lists

Most clubs use various email lists, e.g. for every committee, for members, for non-members, etc. Here the mailing lists are stored in the table mailinslists. When the lists use the email system Majordomo, the lists administration email address and administration password can also be stored here. Changes on email addresses or on mailinglist members are done directly on Majordomo.

User right

In the table user_rights all rights are defined, which a user can have. There are three kinds of rights:

Internal system rights
Rights for a certain committee
Rights for a certain status

Internal system rights are defines by the programming of Vereniging 2.0 and cannot be changed. Besides that there are dynamic rights, which are for example used to show or not show certain user groups in the menu. When for example there is a status member and former member, then there is a right to view members and a right to view former members. This also applies for committees. Since statusses and committees are defined by the administrator, these rights are not static. They are created automatically by two database triggers whenever an entry is added to one of these tables. They are also deleted automatically by the database.

In the field value you find the name of the rights. When this is a dynamic right, the name is created automatically by the triggers, using the scheme view_status_.... or view_committee_....

A user group is a set of user rights combined under one name. For the connection between these two the table groups_rights_map is used. A member is a member of just one user group.

Logging changes and send them by email

All changed on the database are written to a log, which is found in the table logfile. The actual change is written to the field logentry. This can be a simple text, but also an XML with several changes. When the text has to be translated, then write a "translate(..)" in the string, with the text to be translated in the brackets, e.g. "translate(login)". changed_by_member contains tells us who made the change. This enables us to show the previous changed at login. When there has been a change on the data of a member, the index of this member is written to changes_on_member. Since not all changes are done on the data of members, this field can be NULL. With this field we can show the previous changes directly when editing member data.

The final field, change_type, is a required field. There are several types of changes, e.g. changes on member data, changes on contact data, changes on list values, etc. Via this type the system determines which people should be notified by email, if any. Which changes have to be sent to which people, is defined in the table email_notifications.