What’s The Difference Between a Database Schema and Database User?

When I first joined the tech industry I was starting from scratch. My background was finance so no comp sci degree, no coding bootcamp, nada. Google was my friend during this time (and very much still is).

There were a lot of concepts that clicked after a quick Internet search and others….not so much. “What is a database schema?” was one of the ones I was scratching my head about for a bit.

• “So what is a schema exactly and how is it different from just my database…?”

• “Why did they have to choose such an unintuitive word?”

• “Great now I’m 3 articles deep into learning about database objects and turns out there are things I have to worry about other than just tables”

• “So how is this different from just a database user and why can’t we just use that word? Sounds like it would make things a whole lot easier”

Right when I thought I got a grasp of it another question would pop in my head. Two steps forward one step back. It was all the more embarrassing not really understanding this while working for the largest database company in the world…

So in this article today I’m hoping I wasn’t the only one hitting their head against the wall with this one and will lay out what exactly a database schema is and how it relates to the rest of the database!

So What Is a Database Schema?

It will probably be helpful to break this down piece by piece. Let’s start with what the general word schema means.

A schema is just a blueprint. It’s a structured framework or representation of anything. If you were to organize something in a way that has a logical to it, you can call it a schema.

For example let’s take a plate of food.

You’ve got your meat, your carbs, and your veggies on your plate all nice and organized. There is an order here. Asparagus isn’t sticking out of your steak and everything isn’t blended together like a mush. It makes sense, others can understand it, and it’s repeatable. You can think of the way the food is organized on the plate as a schema of a meal.

A database schema is the organization of data in a database. The easiest way to think of it is in terms of database objects. Database objects are the different types of “things” you have in your database. Examples include:

• Tables - the basic way to store data. A grid organized into columns and rows just like an Excel spreadsheet

• Views - custom tables you can create from parts of one or more tables. Helpful for focusing on only certain areas of your data.

• Constraints - rules you can set on the data in your tables

• Triggers - code that executes if a specified event occurs in your database. “If X occurs do Y”

Just as you can create and have different tables in your database (“employees”, “departments”, etc.), the same applies to the other database objects.

A database schema consists of both the objects you have and how they relate to each other (“this constraint might apply to this table”, or “this table is linked to this other one”, stuff like that).

This logic also holds for our meal example. Each piece of food doesn’t just exist in a vacuum. They all relate to each other on the plate. You tend to organize your plate by different food (database objects) and they all interrelate by different rules and principles. Usually you will see a scattering of different food types during a meal and some get connected together but not others. Putting a nice sauce on your steak is a good principle and something that you are going to see a lot. Putting ice cream on it, not so much unless you are feeling eccentric that day.

Your database schema is the meal of how you consume your data. There are a lot of different ways to organize your schema, but at the end of the day it’s about presenting your data and doing so in a way that makes sense. It’s all about creating that structured framework.

What’s The Difference Between a Database Schema and Database User?

So this is where it’s easy to get confused because you might get different answers depending on who you talk to at what company.

When I was first learning about this topic one of the main questions I was trying to solve was, “so is there only one schema that encompasses the whole database or can there be multiple schemas in a database?”

The answer is it depends what you mean by schema.

Across the industry, a database schema can refer to either all of the logical configuration (organization) of your database or part of it.

With Oracle technologies and vernacular, each database user has their own schema which contains all of the objects of the user (tables, views, triggers, etc.). So Oracle databases can have multiple schemas.

Let’s go back to our meal example. Let’s say you have your own personal engraved dinner plate that’s only yours. That’s your database user. Your plate has it’s own meal that is the food on it and how it’s arranged.

Now my coworker Layla also her own engraved plate with her meal on it. My meal is my schema and her meal is her schema. We can even have the exact same food arranged in the exact same order perfectly and my meal is still my schema and her meal her schema. This is because at the end of the day, it’s still my meal on my plate (my schema for my database user). That’s how schemas work with Oracle.

So when you hear database schema in relationship to Oracle Database think database user. Are they essentially the same thing? Yes. If we want to get into semantics, is there technically a difference? Yah, the user is the name of the account (the engraving on the plate) and the database schema is the set of all the user’s objects (that person’s meal).