Home arrow MySQL arrow Page 3 - Plan a Good Database

Relationship Types - 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



We can structure relationships into three categories. A one to one connection is between entity one (E1) and entity two (E2) if E1 can have a relationship with at most one entity from the set of E2. This type of relationship is rare. This is the case in a database where we represent separately husbands and wives in a separate entity. In a monogamist society, a man can have at most one wife (nevertheless, it is possible that he has none).

The second type is the "one to many" relationship. In this case, you can associate E1 to zero or more entities from E2. However, going the other way, an entity from E2 may correspond to only a single E1 entity or none. For example, a company may have multiple branches, and a single branch may have multiple employees. Nevertheless, an employee may work in only one branch of our company at a time (please exclude the special cases of someone going to work in our branch in New York from Monday to Wednesday and in Washington on the other days).

The final relationship type is "many to many." This relationship type is troublesome and needs to be eliminated in the planning phase. For instance, an artist may sing one or more songs, and more than one artist may sing a song. In the ERM, to visualize this we put an arrow on all the relationship's connecting lines on the side where we connect to "one" entity. This means on both ends for the one to one relationship and on the one side of the "one to many" relationship.

Here I should introduce the "is a" relationship. Some entities may have multiple purposes at the same time, and one of them is in fact just a specialization of another one. For example, the manager of a shop is also an employee. In this case, the manager will keep all the traits of an employee; the key of the employee will also be his key, and it may have additional attributes. In an ERM diagram, this looks like the screen below (imagine the attributes are there also):


>>> 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: