When it comes to web development many people seem to forget one important thing. They all start with designing the graphics and page layouts and making sure the code follows every standard and conventions there is, but sadly that’s it. Many don’t think of database design as a important piece in the development. When you work on your code and you are connected to a MySQL database, how many times have you been forced to edit the database schema? If you answer is: many times. Boy, we have some work to do. The problem with designing a database might not occur when you are working alone or in pair but as soon as you start out with a three member team or larger a database design is highly recommended.

What do I mean with designing a database? First of all we need to figure out what makes a database. If we take a look at a database, any database, we can easily divide it into three layers:

  • The conceptual layer
  • The implementation layer
  • The physical layer

The conceptual layer is where we design. This is where we design the database schema with the tables, constraints and relationships. The design we create here can be transferred to any database because it’s not connected to a specific database type. The conceptual layer is an easy to understand explanations of the database. This is where we are going to look deeper today.

The implementation layer is where we actually implement our conceptual database schema.  This is where you choose which type of database we want to work with, for example a relational database such as MySQL. Since the conceptual layer is just an explanation of a database of any type we can use the conceptual design in many different implementation layers.

Lastly the physical layer. This is where we actually store our database and data on the hard drive. Here we setup how we want to access files, index and more.

OK, we go back to the conceptual layer and we are going to introduce a program to use when we want to design a database conceptual schema. The program is called Dia and is available for free.

If  you are totally new to the concept of relational database and have absolutely no clue about what I went through earlier I will try to explain a few things before start with designing a test database. When we design we will look upon three different types of objects.

  • A relation is the same as a table, but we call it a relation. When we design we are looking for any type of real world object like a car, books or users. A relation is displayed as a rectangular.
  • A relationship is the constraint between two relations. A car must be owned by a user for example. A relationship is displayed as a diamond shape.
  • A attribute. A relation has attributes and as we might know it different fields. A user might have id, username, password and email attribute. These are connected to a relation via a thin line and the attribute itself is surrounded by a circle.

So now we have three different objects we can use and it looks fairly easy now doesn’t it? We basically have all the tools we need to create a conceptual design everyone can look at. As I mentioned the important thing about designing a database is that it’s crucial not to change the database schema. Of course you might have to do it during development and in rare cases when the site is online but as a ground rule you shouldn’t change the database schema due to the fact that many other applications and users might depend on it.

OK, when you have downloaded the Dia program, install it and run it. On startup two different windows will popup: a workbench and a menu box. In the menu box there is a drop down menu with the default value ‘Diverse’. Click on that and on the second link from the bottom there is a little arrow pointing to the right, click on that and choose the ‘ER’. This stands for Entity-Relationship module. Why do we say Entity-Relationship module when we just talked about Relation, Relationships and Attributes? A relation is sometimes also called a Entity. What we changed in the menu is that now the Dia program is in the ER mode. Under the drop down menu we just entered a few new icons have popped up.

  • E inside of a rectangular: this is our relation/entity button
  • E inside of a double rectangular: this is also our relation/entity button but for weak entities.
  • R in a diamond: our relationship button
  • A in a circle: our attribute button

Simple isn’t it? Now we can start designing our conceptual database schema. We will start with a simple example. Lets say we want to save users in our database. Each user has a unique id, username, password, email and gender. How can we display this? We start with dragging out a Entity box. Double click on it to access the settings. Name it to ‘User’ and click apply. The entity is done. Now we should add the attributes. Click on the circle with the A inside. First we create the id attribute, make sure to check the ‘key’ option in the attribute settings panel. When you press apply the attribute will be underlined, this shows that the attribute is a key. Continue adding the username, password, email and gender attribute but don’t apply the key option. Finally draw a line between each attribute and connect it to the entity box. I used the line tool and not the double lined button found under the Entity button. Make sure you don’t use arrows, just a straight line. We should come up with something like this:

Dia example 1

Dia example 1

Next we add a new entity Car. It will have two attributes: a registration number and color. The registration number will be the unique attribute for a car and the color attribute can be multivalued. Even though the normalization rules tells us that multivalued fields in a database is wrong, we need to remember that we are working on the conceptual design. We do not think of terms like implementation or MySQL.

We add a new Entity and name it Car. We add the registration number attribute and add the ‘key’ option. For the color attribute we make it multivalued by simple adding that option key. The attribute will be boxed in by a double lined circle, this is telling us that the attribute is multivalued. We take our design one step further. Each User is able to own several cars but one Car can only be owned by one User. How do we display this? We use the Relationship object! Insert a Relationship object between our User and Car entity. Name it owns. Double click to access the settings panel in the left cordiality enter ‘n’ and in the right enter 1. Finally connect each Entity to the Relationship. It will look something like this:

Dia example 2

Dia example 2

Now we have done something really cool. We have created a design of a database with two entities and one relationship between them. It’s easy to see the attributes of each entity and the relationships between them. Imaging a very big database, a design like this would certainly ease it up a bit!

This is only a short guide on how to use the ER model. All I wanted to do was to introduce the Dia program and how you can use it. You will find a useful article about the ER model at the link below. OK, that’s it. A very short introduction on the ER-modelling and how you can use Dia to create it. Thanks for your time!

July

, , , , , , , , , , , , ,

July 7, 2008

4 Comments