Database creation

1. Introduction
2. Tools overview
3. Site structure
4. Database creation
5. Site configuration
6. Creating a Frameset
7. Creating the Menu
8. Contents Frame
9. Backend Structure
10. User Admin Page
11. Master Index Page
12. Detail Index Page
13. Conclusion

Database creation

Creating a website in the “static Internet age” implied a separate HTML file for each page of the site. The usage of scripting languages, such as PHP or ASP, made it possible for web developers to use databases as content repositories, and thus dynamic websites appeared, with PHP or ASP pages taking the content from the database and generating HTML files on the fly.

Using a database, allowed people to organize the information and in the same time opened the way for a new approach in information update. Instead of searching the content in the already formatted pages, now the content was separated from the presentation, and was combined only per client request. Needless to say that the same content can be used in multiple places, and changing it in the database will automatically update all related pages.

If we look again at the features we want to implement in KTMLtutorial’s website, we’ll notice that the most important pages in the website are the menu and the content section. We can’t also forget the user authentication section that will also need a table to store user information.

Therefore we’ll organize the database information in the following way, using three tables:

• the articles_art table will be the repository for the articles published on the KTMLtutorial website;
• the pages_pag table will contain the structure and properties of the single pages;
• the users_usr table will contain the KTMLtutorial users information.

Please note that we use a naming convention to be able to manage the database fields with ease, and this consists in adding to each table name an underscore and then a short name (3 letters), that will be also added at the end of the fields name. When doing a regular table join, this trick will allow us to avoid calling each field with “tablename.fieldname”, because the field names will be unique.

Find below articles_art’s table structure:

CREATE TABLE articles_art (
id_art int(11) NOT NULL auto_increment,
idpag_art int(11) NOT NULL default '0',
title_art varchar(255) NOT NULL default '',
text_art text NOT NULL,
date_art date NOT NULL default '0000-00-00',
PRIMARY KEY (id_art),
UNIQUE KEY id_art (id_art),
KEY date_art (date_art)
) TYPE=MyISAM;

pages_pag structure:

CREATE TABLE pages_pag (
id_pag int(11) NOT NULL auto_increment,
title_pag varchar(255) NOT NULL default '',
text_pag text NOT NULL,
menu_pag varchar(100) NOT NULL default '',
type_pag tinyint(4) NOT NULL default '0',
PRIMARY KEY (id_pag),
UNIQUE KEY id_pag (id_pag)
) TYPE=MyISAM;

users_usr structure:

CREATE TABLE users_usr (
id_usr int(11) NOT NULL auto_increment,
username_usr varchar(50) NOT NULL default '',
password_usr varchar(50) NOT NULL default '',
PRIMARY KEY (id_usr)
)TYPE=MyISAM;

The only important relationship is a one to many relation between a page and it’s articles. A visual representation of this database and its relations has been generated with QuB (our commercial query builder):