Creating websites: Database design | Blog
  Website conception - Vortex Solution Website conception - Vortex Solution

We create


Creating websites: Database design

Creating websites: Database design

15 April | Posted by @Marc-André Lamothe

The foundation of a powerful and easy-to-maintain website lies undeniably upon the database on which it is built. Nowadays, almost every online website is database-driven; these are referred to as dynamic websites.

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.


Add a new comment


catherine beaumier lacroix

Friday, April 15, 2011 Excellent article pour comprendre un peu plus en profondeur la tâche importante et complexe d'un programmeur! :)

Boost your SME's business strategy Analysis of your project and request for more information

Past events

5 à 7 réseautage de la Rentrée AMR


2013 Montreal SEO Camp Conference


Mobile World Congress


Amazon Web Services Conference


World Congress on Information Technology


ICSC Seminar "Les tendances"


Hello Vortex.

Vortex Solution 40 rue Jean-Talon Est
Montréal ( Québec)
H2R 1S3

Phone: 514 278-7575
Fax: 514 278-7606

Vortex Solution offers website design services in Montreal. Our team of 100 experts knows all there is to know about website design. When dealing with Vortex Solution, your website will be built efficiently and worry-free. We've been creating websites since 1999 and have carried out over 3000 web projects to this day. Your website design project is very important to your company's growth. Assign your website to Vortex!