Lesson 3: Database Modelling and Structurization

ByDr.-Ing. Erik Neitzel
Lesson 3: Database Modelling and Structurization

This lesson shall give some advice on how to start each database project regarding data modeling and a way to think it through, followed by a brief introduction into the usage of data definition, manipulation and queueing statements.

Basic database design process

The first thing you need to make sure is that you know what your application shall be able to do. Having thought about that, you can decide for a data structure.

Try to think of the following:

  • What are the main entities my database is dealing with?
  • What are the relationships between those entities?
  • Do I need additional relationship tables?
  • What are the main attributes of both my entities and my relationships?

You can use an Entity Relationship Modeling tool or just a sheet of paper — it depends on how complex your database is.

Afterwards, decide for a unique primary key for each of your entities, usually an ongoing number or ID, which is what you will find in every WordPress table, for example.

Now it is wise to sort of “emulate” all your database tables and all your attributes including primary keys using an Keynote/Excel file or a sheet of paper. Visualize your tables and fill them with example data. That way you can be sure not to miss something important. When that is done, think about the kind of data your primary key consists of. Is it data you get from a different system, where you can be sure that every new entry has a different key value, or do you want to let it be filled automatically using auto_increment.

Create table statements

Finally, build your final data model using create table SQL statements (DDL) as discussed earlier. WordPress and every one of its plugins will do that for you, and the only way you will ever see that is by pulling a dump of your database. Within that you will see statements like the following:

CREATE TABLE `wp_users` (
  `ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_login` VARCHAR(60),
  `user_pass` VARCHAR(255),
  `user_nicename` VARCHAR(50),
  `user_email` VARCHAR(100),
  `user_url` VARCHAR(100),
  `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `user_activation_key` VARCHAR(255),
  `user_status` INT(11) NOT NULL DEFAULT '0',
  `display_name` VARCHAR(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`)
)

What you see here is a table “wp_users” being created with ten columns (attributes): ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status and display_name. Afterwards it is defined that column ID is a primary key (whose value can only exist once). That key can then be used by other tables as a reference for the data row in question. In addition, ID cannot be null and it is automatically incremented whenever a new row is created in table wp_users.

You also see bigint(20) and varchar(100), etc. – which are the data types of each attribute and its allowed maximum value size. There are also some additional information which you do not need to concern yourself with at this time. Yet is is important to get a basic understanding on how and why data tables are being defined within a database.

Insert, select, update and delete statements

Let us now say a user shall be added to your WordPress table wp_users by talking directly to the database.

INSERT statements

You will then issue the following insert statement:

INSERT INTO wp_users 
(ID, user_login, user_pass, user_email, user_url, display_name)
VALUES 
(NULL, 'serpbot', 'abc123', 'serpbot@serpbot.org', 'www.serpbot.org', 'SERPBOT')

We tell the database to INSERT something INTO the table “wp_users”, specify the columns which we want to address, followed by its according VALUES.

SELECT statements

If we wanted to see all contents of our user table, we could ask the database like this:

SELECT * FROM wp_users

That would SELECT all (*) data FROM our table “wp_users”, giving us a list of all the rows that are currently inserted – including our newly inserted user “serpbot@serpbot.org”.

UPDATE statements

If we now wanted to update that users password, we would use:

UPDATE wp_users SET user_pass = 'bcd234'

With this SQL statement we instruct the database to UPDATE the table “wp_users” and SET the attribute “user_pass” to “bcd234”.

DELETE statements

Finally, if we wanted to delete that user from the database, we would do it like this:

DELETE FROM wp_users WHERE user_email = 'serpbot@serpbot.org'

We tell the database to DELETE FROM the table “wp_users” WHERE the attribute “user_mail” equals the value “serpbot@serpbot.org”.

Pretty straight forward, right? This is just meant as an introduction, of course. Yet it may serve as a sign not to be afraid of relational databases and SQL statements when doing maintenance on your WordPress installation or building your own plugin or standalone application from scratch.

The next lessons:

Dr.-Ing. Erik Neitzel