Custom User Databases

One of the services that I offer in connection with web design and development is that of customised user databases. What exactly do I mean by that?

If your web site is developed using a content management system such as WordPress then it is driven by a database. This means that all the actual content of the site is stored in the database. This includes the menus, the text that goes into each page, the layout of each page and the links to all the images (though these themselves have to be uploaded as actual files). When you log on to the WordPress control panel, you are able to manage all this information via a user friendly interface, enabling you to create a page, edit a page, upload an image file, add a menu item etc.

If this is all you require then you really don’t need to worry any more about databases as the content management system looks after it all for you. On the other hand it may be that you need some database driven data in connection with a more specialised aspect of your site. Such data might be managed separately from the WordPress admin panel, but can still be managed in a user friendly way from a web based interface. The best way to explain this is to show a couple of examples from my existing sites.

Example 1 – Diary Schedule

You will find this on my church web site at http://www.longcroftchristianfellowship.org.uk/ under “Meetings & Events”. Here you will find a series of pages showing the schedule for the current and upcoming months with each month being laid out as a calendar page. Behind the scenes the meetings/events are stored in a single database table, with each meeting/event occupying a single record. Each record comprises a number of fields defining the various pieces of information relating to the item, e.g. date, time, description, venue, contact, and so on.

This may sound simple so far, but there is a lot more we can do. For example, because of the nature of this particular data, there is a lot of repetitive information that would become very tedious to type in every time, considering that in a typical week we have two Sunday services at fixed times and a Thursday night prayer meeting at a fixed time. To overcome this difficulty there is a separate database table that defines the activities within a typical week and month. Weekly meetings are referenced by the day of the week and time; monthly meetings are referenced by the week/day (e.g. 2nd Monday) and time. When we want to add a block of new months to the schedule, we visit a special admin page to carry out the operation. This page works by reading the schedule of typical weeks/months and automatically building the data for each month from this information. Once this is done, the user can then make any specific edits required – to add extra meetings, to delete ones that are not taking place and so on.

The admin interface can also make use of views. A view is basically a given subset of a full database table, so for example we can just look at the data for a given day of the week. This can be useful if for example we need to make the same edit to all instances of a particular meeting on a particular day. The database management facility also allows us to make the same edit simultaneously on a set of selected records in a single operation, thus saving tedious repetitive edits.

Example 2 – Family Tree

I have published my own family tree online and you will find this at https://www.andperry.com/family-tree/. On initial consideration a family tree may seem quite a complex data structure, but in fact the way that it is linked together in a database can be very simple. Only two tables are actually needed to achieve this, as follows:-

  • People – This table contains a single record for each individual person in the tree. Each record contains all the information relating to the individual such as name, gender, birth details, death details and occupation.
  • Families – Each record essentially defines a husband/wife relationship and contains a link to the personal record for each partner. It is also possible for a record in this table to contain only one person if the details of the corresponding spouse are not known. Against each partner there is a family number which is normally set to 1, but enables a person to be included in more than one family record if he/she has been married more than once. Other items of information stored in this table include marriage details, a ‘divorced’ indicator and an ‘unmarried’ indicator.

The tree is built simply by including a field in each personal record creating a link to the family record that defines his/her parentage. Although the two tables provide the minimum requirement for building the tree, there is in fact a third table specifying all the place names in use. Because of the nature of a typical family tree, some place names will be used repeatedly, perhaps many times. Where a place name needs to be entered in a personal or family record, the web page for doing this takes the name from a drop-down list, which in turn is generated from the place name table. By requiring place names to be selected in this way, we can save typing and also minimise errors/inconsistencies when adding new data to the tree.

Conclusion

By using these two simple examples, I have hopefully given a little bit of an idea of what can be done with database applications. It is nevertheless a vast subject and there is virtually no limit to what you can do. If you have any specific requirements for your own web site then please do not hesitate to come and talk to me.