Originally published by Robert Beisert at fortcollinsprogram.robert-beisert.com

SQL Databases – It’s All in the Plan

It doesn’t take very long to learn SQL, because it’s a very simple scripting language with only four basic operations. No matter how complex you make your queries, with any number of conditions and tests, ultimately it boils down to CRUD. Why is it, then, that so few people can develop databases effectively?

Like all programming, it ultimately comes down to the detail of your plan.

The Entity-Relationship Model

In our simple sample database, we have three tables. The “people” and “books” tables are both what we would call entities – tables that describe things. Each record in the “people” table describes one person, and each record in the “books” table describes one book.

However, what is the value of these tables without a way to connect them? In our library, people interact with books in one very important way – they check them out.

The “checked_out” table is a relationship table. Relationship tables describe how entities interact with one another. Because people check out books, each record in the “checked_out” table connects one person to one book. In addition, each record contains information on the relationship itself – in this case, when the book was checked out and when it was (or wasn’t) checked in.

Degrees of Relationship

When we are planning relationships, we have to think about the ways entities will relate to one another. While the obvious aspect would be what kind of queries we would want to make about the relationship (“When did X check out Y?”, “Did X ever return Y?”, “Was Y overdue when X returned it?”, etc), we usually diagram a less-obvious aspect:

How many X’s can relate to how many Y’s?

Suppose our relationship was of monogamous marriages. It wouldn’t make sense for us to have the same person X married to two different Y’s. If “Ruchi” were married to “Bob”, it wouldn’t make sense for us to also have “Bob” married to “Sarah”, because each person should only be allowed to marry one person at a time. This is a 1:1 relationship – one X can marry one Y, and one Y can marry one X.

However, what if our relationship table described the Muslim conception of marriage, which allows a man to be married to many women but each woman to be married to only one man. In this case, “Bob” could be married to “Sarah” and “Ruchi”, but “Ruchi” could not be married to “Bob” and “Dylan”. This is a 1:m relationship – one X can marry many Y, but each Y can only marry one X.

Finally, our relationship table could describe true polyamory – anyone can marry any number of people. “Ruchi” could marry “Bob” and “Dylan”, and “Bob” could marry “Sarah” and “Ruchi”. This is a m:n relationship – many X can marry many Y, and many Y can marry many X.

It’s important that we think of how entities will relate to one another when you’re planning.

Sometimes, we want to specify an exact range of “m” values and “n” values. We then have a (m1:m2) : (n1:n2) relationship, which makes more sense in a diagram than on paper.

Diagramming the Entities and Relationships

When we’re planning databases, we usually go through what we call the “diagram” phase, where we draw out our entities and their relationships. This helps us get a grasp on what we want our database to do, what we want it to contain, and how those things should link together.

For our simple Library database, the diagram would look something like this.

Entity-Relationship (ER) ModelAs you can see in the diagram:

  • Entities are represented with rectangles
  • Relationships are represented with diamonds
  • Fields are represented with ovals
  • Keys are underlined fields
  • Degree is shown on the lines connecting an entity to a relationship
  • Foreign Keys are shown on the lines connecting an entity to a relationship

That’s one of the big reasons we went with a simple database – diagramming larger databases can become a hassle.

Fortunately, if you have MySQL and PHPMyAdmin installed, you can do all this on a computer and translate it directly into your database.

photo by: