Home arrow MySQL arrow Page 2 - Plan a Good Database

Data Model - MySQL

Organize a collection of data somehow for later usage and you will get a database. In the last couple of years, with the spread of the Internet, databases have become something you will come across everywhere on the World Wide Web. Most websites have a strong database behind them. With this phase of the web's evolution, if you work in software development, eventually you will need to create and plan a database from scratch. This article is here to teach you and show you what traits a solid plan should have.

  1. Plan a Good Database
  2. Data Model
  3. Relationship Types
  4. Relational Model
By: Gabor Bernat
Rating: starstarstarstarstar / 9
June 02, 2010

print this article



Once you have the database management software (DBMS) up and running on your computer, your next step is to plan the database. The data model is a general theoretical structure of the database, and it is independent from the DBMS in which you implement it. To present how to construct this is the main objective of this article. The data model consists of a marking system that  describes the data and an operation set that we use to organize the data.

We use the entity-relationship (ERM) data model to construct a conceptual structure for our database. The building blocks of the ERM are entity, attributes and relations. An entity is anything that can exist on its own and that we can differentiate an instance of it from another. For example, in planning the database of a company, entities are the employees, managers, branch shops (if they exist) and so forth.

While an entity may have multiple traits, one of its traits (or a combination of them) should identify uniquely any instance of the entity. To go back to the database of the company, this is the personal identification number of the employee. This is the key of the entity. In a company spread around the world, this could also be the employee's country of residence and passport number.

By no means should this include only a single trait. Furthermore, if a few traits of the entity cannot identify it uniquely, we can create an internal ID number that we assign to it upon entering it into the database. For example, there is the account or membership number you receive from the gym, which is generated when you sign up. It can even be text, as in the case of your e-mail address. You can create whatever you want. The idea is to immediately and uniquely identify an instance of the entity.

In the ERM, an entity is a rectangle with the name of the entity in the middle:


The attributes of an entity (traits of the instance) we model with an ellipse with the name of it inside it. We link this with a line to the entity to which it corresponds, and if it is a key, you underline the attribute word. For example:


The final piece is the relationship. We use this to link together two or more entities. The object used is a diamond in the middle containing the name of the connection, and two lines from and to the rhombus to connect it with the entity. A relationship can be, for example, between the shop and the employee. The employee works in the shop.


The relationship itself can have traits if it symbolizes the work, and may differ depending on to whom it is connected. A good example for this is the connection between the supplier and the shop. The price at which the supplier sells depends on the relationship, as next time he may charge extra or make a better offer to the shop. In this case the relationship will have a price attribute, and for this we use the same ellipsoid notation paired with the line connection to the diamond.

A full ERM diagram will look like this:


- Image Courtesy of Wikipedia -

>>> More MySQL Articles          >>> More By Gabor Bernat

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: