4

Keep is Simple: Easy To Understand Data Modeling Concepts

Relational Database Data Models

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

Data Modeling - Key Table Elements

Relational Database Table Model

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

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.  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?

Kris Wenzel
 

Kris Wenzel has been working with databases over the past 28 years as a developer, analyst, and DBA.He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame.Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

  • Jitendra says:

    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?

  • 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.

  • Sherdill Noori says:

    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.

    • 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.

  • >