Get Ready to Learn SQL: 7. Simplified Data Modeling

Introduction

When working with SQL databases, it is often useful to create diagrams of the database tables and their relationships. These may be done during the design process, as you’re creating your model, or once the database is created, in order to document the tables’ dependencies. As I explain various concepts in my lessons, I’ll sometimes use models to illustrate my points.

There are many types of modeling software you can use to create models, such as MySql Workbench, which not only create smart looking diagrams, but also generate the code to create the database! In my case, since I’m trying to keep the diagrams simple, and I don’t have a need to generate code, I’m going to create my own diagrams. They are loosely based on the IDEF1X notation.

Modeling a Table

The diagram above shows my method to model a relational database table. The major elements that are depicted include:

• The Table Name, which is located at the top of the table.
• The Primary Keys. Remember the primary keys uniquely identify each row in a table. A table typically has one primary key, but can have more. When the key has more than one column, it is called a compound key.
• Table Columns – There can be one or more table columns. To keep the diagrams simple, I don’t show the data types. I may introduce those later when we focus on more comprehensive modeling.
• Foreign Key – This is a column or set of columns which match a primary key in another table.

Speaking of foreign keys, the match between them and the primary key is what “glues” the database together. The significance of these relationships becomes apparent, front and center, once we start to work on joining tables together.

Modeling Table Relationships

We connect lines between tables to show relationships. In some cases, an entry in one table can be related to more than one entry in another. This is called a one-to-many relationship. In our example, there are many employees in one department; therefore, we show a many-to-one relationship.

A many-to-one relationship is similar to a one-to-many relationship, this difference is in the point-of-view you take when naming the relationship. I think most people speak of one-to-many relationship more often.

Sometimes, there may not be an entry in a table, so technically speaking, you could have zero or one to many, but that gets hard to say, so when speaking in general terms, most people say “one-to-many.” However, when you want to get precise, you can use notation to specify the cardinality of a relationship.

When we cover normalization, we’ll use these diagrams to show how we organize the database. I think it is important you become comfortable with these diagrams and concepts before we begin that journey. As such, make sure you understand the following:

• Table
• Field
• Primary Key
• Compound Key
• Foreign Key
• One to Many Relationship
• One to One Relationship

Exercises

• What is the difference between one-to-one relationship and a many-to-one relationship?
• What is a Foreign Key? How do you define one?
• What is the difference between a primary key and a compound primary key?

Answers

I’d be more than happy to share the answers with you. Sign up for my weekly newsletter at http://www.essentialsql.com so you’re sure to receive each week’s lesson.

Congratulations! You just learned how to use data modeling to better understand which tables and relationship exist within a database. More tutorials are to follow! Remember! I want to remind you all that if you have other questions you want answered, then post a comment or tweet me. I’m here to help you. What other topics would you like to know more about?