Database Schema

Learning outcomes:

  • Create a visualization for a database or dataset
  • List keys and relationships between data in a database or dataset

Would you like to download my PowerPoint to follow along?

  • What is database schema?
    • In relational databases we need to organize the data
    • When data is organized we need a map or blueprint of it so we know how everything is put together
    • Schema can also be the rules or formulas for how the database is done
    • Schemas will create these rules to ensure data integrity is maintained
  • Why is this important?
    • Having a map or blueprint allows us to see at a glance the relationships of our database
    • Having rules for our database helps our data integrity, which is an important component to databases
    • Data is often "dirty" and that affects how it's used and the accuracy of results
    • A schema is also a good way to share this information in a concise way with others
    • A perfect database you can never share or have others use wouldn't be useful
  • What's included?
    • Database Schemas are likely to have
      • Tables with labeled fields
      • Primary key and foreign keys with relationships illustrated
      • Any rules or requirements for adding data
      • Notes on if particular data types are required
    • Schemas tend to include pictures and graphical representations to illustrate all of this
  • Advantages of having schema
    • Design Consistency - records of how the database was put together are useful for you and anyone that has to interact with it
    • Scalability - Well organized data and databases are easier to grow then if they were not well put together
    • Optimization - Knowing how the database is organized can help queries run faster
    • Maintenance - It's easier to fix and maintain a well-organized well documented database
    • Security - Having a plan of how you're storing and protecting your sensitive data means it's more likely to be followed by everyone
  • Types of schema
    • Physical schema
      • Explains how the data is stored on the actual storage
      • Usually will have the explanation including files and indices
    • Logical schema
      • Includes the relationships of the tables
      • The rules that the data has to follow is included here
      • Will include the ER modelling
    • View schema
      • How the end user will work with the database
      • To see this you don't need all the other information, so it might be easier for some people to follow
  • Schema design
    • There are a lot of potential design options for schema
    • They are chosen based on what makes the most sense for the data and application
  • Examples of database schema design options:
    • Flat model schema
      • This is basically like a single table
      • Very simple
      • Doesn't model all the data needs for more complex situations
      • Likely only useful if you could do a spreadsheet instead of a database
      • Might be used for a small company with only a few employees that need an employee database
    • Hierarchical model schema
      • Looks like a tree structure like you might see in programming data structures
      • Contains a root node where the information is starting from
      • Used for things like nested data because it's the parent/child model
      • An example use case for a school might be a database of employees and students
    • Network model schema
      • Similar to the hierarchal schema
      • This allows many-to-many relationships to be illustrated, whereas networking only allows one-to-many
      • Network model schema means you can include workflows and paths to the data
      • Example use case might be a restaurant chain where you need to keep track of inventory and employees but you also want to model workflows for cash and food delivery
    • Relational model schema
      • Mostly used in relational databases
      • Used to keep track of the table and relationships
      • Some say it's better for object-oriented design
      • Example use case in a school we have classes, faculty and students, departments and divisions, or other data that is related
    • Star model schema
      • Can be used for larger amounts of data
      • Used frequently for data marts and data warehouses
      • Has the general appearance of a star, where there is a central table called the fact table, and connected to that are the dimension tables that describe other important info
      • The relationships are described using foreign key relationships
    • Snowflake model schema
      • Snowflake is an adaptation of the star schema
      • Like the star style, there is a central table that stores datapoints and references to dimensional tables
      • However it expands on star by allowing multiple dimensional tables to include even more information
      • Also used frequently for data marts and data warehouses
      • Same example as star model, but used when more data is needed, such as breaking out a location table for customers, or departments for employees

    Suggested Activities and Discussion Topics:

    Would you like to see some more classes? Click here