Developing a website with an online database

Websites may start life as a brochure website, until the site owner realises they need to put some structured data into the site.

The data may be simple in structure, or complex. Either way, there are helpful tools to get the data online and integrated into the site.

large mid blue circle three boxes

Start it simple

You wouldn't be alone if your data started life in an Excel spreadsheet, or even a Google Docs spreadsheet. In our experience, these simple tools are the most common place for databases to start their life!

These tools are alright for managing simple tabular data, but they don't help much when it comes to bringing your data onto a website, less still your custom-built company-branded website! So how do you bring the two together?

The answer is to get your data online, and stored in an online database within a website that's powered by a content management system (CMS).

How to choose a CMS platform that's suitable? There are several considerations, even for a single-table database:

  • Whatever data you store, it needs to be created, and probably needs to be updated from time to time; think about whether the online interface is going to meet your needs.

 

  • Who's going to administer the data? You may start with a single person doing data admin, but frequently there is a need to share the management task, even to have a business process around it; how can you facilitate controlled access to administrators?
  • Think about the visitor side: how do you want to present your data to website visitors? Will the interface be integrated into your regular website style? Will there be browsable lists of content, or a search system, or even a whole page of HTML per record of your data?
  • Should access to the data be controlled (e.g. via an extranet/secure area with your website), and should there be certain actions related to the data (e.g. transactions, changes to the data by website visitors)?


These may sound like difficult problems, but with the right web-based software platform they are easy to overcome. In our experience, where customers start with an "Excel-centric" view, this can distort their line of thought and make it look hard to deploy their data online; it's better to think of data in its pure form, so that the management of the data and its delivery to end users is thought of together.


Data types, multi-media and maps

When designing your database, it's tempting to assume that all fields will be text. However, you should not overlook the fact that there are many other field types:

  • Checkboxes (where a field can have zero, one or more multiple pre-set values)
  • Radio buttons (where a field can have one of a range of pre-set values)
  • Values picked from a centralised list (e.g. a list of countries)
  • Special formats, such as date, URL, email
  • Multi-media types, such as images or movies
  • Spacial co-ordinates, which relate to positions on maps

blue blocks and hand


Some useful tools

When building a system for a client, we try to identify the client's need, and use the right tool for the job. For reasonably simple online databases, where we aim to do an out-of-the-box implementation with more or less no customisation, we usually recommend the Zenario content management system, with its useful Datasets feature for setting up online databases.

Setting up the data management area within the website is very quick, after which we declare the fields that are needed to model the data. Once launched, Zenario provides a data management area inside its Organizer back-office system.

This allows the customer administrators to create data records via the web browser, edit, and delete them. There are helpful search mechanisms and different ways of listing the data. 

 

It is easy to add images to data records, and if need be to relate a record with a full HTML page of description.

For bulk data work, there is an Import function to pull in data from a CSV or Excel file, and there is an Export function too. These are handy when there is a large amount of data to pull in, or sharing the data with a colleague.

On the visitor front end of the website, there may be a number of interfaces, depending on the nature of the data. There may be a search interface (often this is done with Ajax-type interactions), or there may be a simple list of data records, paginated or filtered by some category.


Relational databases

Where data can be represented by one long list of records, it can be often be represented in a database by a single database table.

But a truer picture of the real world involves "normalising" data, and breaking it up into several tables. This increases complexity but can make management easier.

The argument for "normalisation" goes like this: let's imagine you have a database of used cars for sale. You might be tempted to enter the year of manufacture, the manufacturer, model, colour, condition and price all as text fields on each database record. What then happens when you want to search for cars with a particular brand? or a car with a certain colour that is newer than a certain year? 

 

This problem is very hard to solve if you have one table of all-text records; but you need to normalise your data: separate years, manufacturers, models, even colours and conditions into separate tables. This makes the data cleaner, easier to maintain, and is essential for making user-friendly search interfaces.

Space doesn't permit me to go much further into relational database design here. But it suffices to say that it's generally worth putting a little thought into the design of the online database before plunging in for the quickest implementation. The requirement should be discussed with a database designer, and an accurate model of the data should be developed.


The user interface

A software module usually needs to be developed to bring the data to the pages of the website. This need not necessarily be complicated, but involves a little more development work than the out-of-the-box interface which may be enough for a single-table system. The interface needs to deal with the "relationships" between the tables, and other rules surrounding them.

 

Again for such scenarios we at Tribal Systems usually use the Zenario Datasets system, since its modular software architecture approach allows us to develop rich environments for managing data and for viewing it on the user side.


Extending the data model

It goes without saying that you expect your site administrators to be able to edit the data: to add, edit and delete records, even to import and export the data.

It's not unreasonable to expect that your administrators can add data fields as well. Until not long ago you could have expected that an additional field would require some programming work from the developer. But with a decent database management interface you should be able to add a simple text field easily.

 

Scaling up ought not be painful, either. Such a system should be capable of handling tens if not hundreds of thousands of records. Even if you think your needs are small and straightforward, it's worth considering how your needs may grow, and pick a platform that can scale from a modest size to something larger, while maintaining good response times.