Keep it Simple: Easy To Understand Data Modeling Concepts

Kris Wenzel

·

·

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  your data modeling, 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.

Data Modeling – Table Elements

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.

Data Modeling – Table Relationships

SQL 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 on 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 a there may not be an entry in a table, so technically speaking the 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.

CardinalityNotation
zero or one-to-many0..*
one-to-many1..*
zero or one-to-one0..1
one-to-one1..1

When we cover normalization we’ll use these diagram 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

 Read More: Database Normalization – in Easy to Understand English >>

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?

Get Answers Here!

Congratulations!  You just learned how to use data modeling to better understand which tables and relationship exist within a database. v I want to remind you all that if you have other questions you want answered, then post a comment. I’m here to help you.

6 responses to “Keep it Simple: Easy To Understand Data Modeling Concepts”
  1. What are Ten Things a Junior DBA should know?

    […] is normalization important and when can it become a liability? Knowing the difference between a Primary, Foreign, and Unique key is important.  Also is understanding and knowing how to enforce one-to-one, one-to-many […]

    Reply
  2. Sherdill Noori

    Sir, As a brand new to Database I really express my thanks to you.
    You are really honorable and your lessons are so explicit and manifest.
    I hope this will go for ever.

    Reply
    1. Kris Wenzel

      Hi Sherdill,

      I’m glad you like the blog posts. I really try to address issues that SQL beginners have questions with.

      To help me continue to provide great posts for beginners, I’m always looking for useful topics.

      Are there some topics you would really want me to cover?

      Thanks!

      Kris.

      Reply
  3. Kris Wenzel

    Hi Jitendra,

    Here is a query I found at: http://dattatreysindol.com/2012/04/16/sql-server-capturing-table-and-column-metadata-and-description-building-data-dictionary-part-2/

    This query uses the datadictionary to obtain all the tables and columns. A cool feature of sql is that the definition of the tables, columns, and other database objects is actually stored in a set of columns called the data dictionary. You can query this database like you would any other tables.


    SELECT
    [sCOL].[name] AS [ColumnName]
    , CASE
    WHEN [sTYP].[name] IN ('char','varchar','nchar','nvarchar','binary','varbinary')
    THEN [sTYP].[name] + '(' + CAST([sCOL].[max_length] AS VARCHAR(10)) + ')'
    WHEN [sTYP].[name] IN ('float','decimal','numeric','real')
    THEN [sTYP].[name] + '(' + CAST([sCOL].[precision] AS VARCHAR(10)) + ',' + CAST([sCOL].[scale] AS VARCHAR(10)) + ')'
    ELSE [sTYP].[name]
    END AS [DataType]
    , CASE [sCOL].[is_nullable]
    WHEN 0x1 THEN 'Yes'
    ELSE 'No'
    END AS [IsNullable]
    , CASE
    WHEN [IdxDtls].[column_id] IS NOT NULL THEN 'Yes'
    ELSE 'No'
    END AS [IsPK]
    , CASE
    WHEN [sFKC].[parent_column_id] IS NOT NULL THEN 'Yes'
    ELSE 'No'
    END AS [IsFK]
    , [sEXP].[value] AS [ColumnDescription]
    FROM
    [sys].[objects] AS [sOBJ]
    INNER JOIN [sys].[columns] AS [sCOL]
    ON [sOBJ].[object_id] = [sCOL].[object_id]
    LEFT JOIN [sys].[types] AS [sTYP]
    ON [sCOL].[user_type_id] = [sTYP].[user_type_id]
    LEFT JOIN (
    SELECT [sIDX].[object_id], [sIXC].[column_id]
    FROM
    [sys].[indexes] AS [sIDX]
    INNER JOIN [sys].[index_columns] AS [sIXC]
    ON [sIDX].[object_id] = [sIXC].[object_id]
    AND [sIDX].[index_id] = [sIXC].[index_id]
    WHERE [sIDX].[is_primary_key] = 0x1
    ) AS [IdxDtls]
    ON [sCOL].[object_id] = [IdxDtls].[object_id]
    AND [sCOL].[column_id] = [IdxDtls].[column_id]
    LEFT JOIN [sys].[foreign_key_columns] AS [sFKC]
    ON [sCOL].[object_id] = [sFKC].[parent_object_id]
    AND [sCOL].[column_id] = [sFKC].[parent_column_id]
    LEFT JOIN [sys].[extended_properties] AS [sEXP]
    ON [sOBJ].[object_id] = [sEXP].[major_id]
    AND [sCOL].[column_id] = [sEXP].[minor_id]
    AND [sEXP].[class] = 1
    AND [sEXP].[minor_id] > 0
    AND [sEXP].[name] = N'MS_Description'
    WHERE
    [sOBJ].[type] = 'U'
    -- AND SCHEMA_NAME([sOBJ].[schema_id]) = N'Sales'
    -- AND [sOBJ].[name] = N'SalesOrderDetail'
    ORDER BY [ColumnName]
    GO

    Regarding a diagram, you can create database diagrams in SQL Server Management Studio. If you need help with that tool, just sign up for my news letter. Once you do, you’ll receive instructions on how to get the free version of the tool and get going with SQL Express.

    Note: I tested the above query using the tools I talk about in the guide.

    Reply
  4. Jitendra

    Hi Kris,
    Am looking to extract Table and Column level information from SQL Server. Any idea How can I do it?
    Also, what all information do I need to have for counducting a “Review” & how can I extract Datamodel diagram from SQL Server Database?

    Reply
    1. Alex

      Hi Jitendra,
      Could you share the answer to your questions:

      Also, what all information do I need to have for counducting a “Review” & how can I extract Datamodel diagram from SQL Server Database?

      If you got the answer from Kris, please kindly send to my email address (yelin.chen@alethix.com). Appreciate it.

      Thanks,
      Alex.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

More from the blog

MySQL PostgreSQL SQLite SqlServer