Creating websites: Database design
15 April | Posted by @Marc-André Lamothe
A dynamic website offers many advantages when compared with a traditional, static website. Mainly, it allows users to edit the website’s content without having to open a single text file or to write a single line of code. Changes are usually made with a CMS (Content Management System) which is a secure website portal that allows users to log in and manage the database’s content, directly online. The information entered or modified in the database is then instantly visible on the website.
However, using a database can also have some disadvantages, among which there’s the issue of execution time, in terms of performance. Loading a dynamic web page (with content that comes from a database) can require from 2 to 5 times more time than loading a static web page (with content integrated into the page’s code). That’s why it is essential that the database be optimally designed straight from the beginning of the project, in order to obtain the shortest execution time possible in web pages.
The first step consists in clearly identify elements (or objects) that represent all the type of information that will be available on the website or web application. This will help avoid redundancy (many similar isolated elements that could share the same data structure) and overload (many different elements that end up sharing the same data structure) when designing tables. A good database designer will be able to efficiently tackle these two issues and find the most intuitive solution possible that will balance between them, thus avoiding confusing those who will work on the website after him. He will also prove to be farsighted by analyzing immediate needs and those that can emerge in the future. The Internet is constantly growing, and so will your website. This is why it’s the database designer’s duty to make sure that the database he creates can evolve without any problem.
Then, it’s essential that the structure of the database be as rigid as possible to insure the integrity of the data it contains. To start, it is essential that each table have a column with a primary key and an index on it. Also, if a specific data cannot appear more than once inside a collection of elements (eg.: color code, product number, etc.), then, a unique key should also be added to this column, because even if the entry of such data will not be allowed in the website, there is always a risk that a human, or any another, error damages the content of the database.
We also need to make sure that the columns of each tables are configured as precisely as possible. This consists in:
- Choosing the appropriate data type (eg.: bit, tinyint, int, varchar, text, etc.);
- Accepting the NULL value, or not (eg.: if there’s no reason why a client wouldn’t have a name, then do not allow the NULL value in the Name field);
- Specifying a default value (eg.: if you have a field of Bit type, then you could enter 1 or 0 as the default value or GETDATE () for a Datetime field);
- Adding a comment (eg.: if the Status field can contain three values then write: 1= Pending, 2 = Active, 3 = Inactive).
Foreign keys can also be used, but they usually do more harm than good, mainly during the synchronization of data between the development and production database, or when importing data from an external source. And when a certain data identified by a foreign key no longer exists, this does not necessarily mean that the data with the foreign key should no longer exist, in which case you can just ignore the foreign key.