How to Design a Database Schema

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: The Difference Between Logical & Physical Data Flow Diagrams

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
Your next lesson will play in 10 seconds
  • 0:04 Database Schema
  • 0:55 Designing a Schema
  • 1:49 Mapping it Out
  • 3:50 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Speed Speed Audio mode

Recommended Lessons and Courses for You

Lesson Transcript
Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems, has a PhD in Information Technology Management, and a degree in Information Systems Management. He is an adjunct professor of computer science and computer programming.

Just as in construction, a solid database needs a blueprint. Database schemas are blueprints for databases. In this lesson, we will discuss how to design and create a database schema.

Database Schemas

If you tried to build a house without blueprints, you would quickly run into serious problems. The same is true for databases. If a major online retailer had no schema, or blueprint, for its massive amount of data, the online retail giant would quickly find itself out of business.

Let's review the schema. A full database schema is actually a physical schema and a logical schema. The physical schema displays the hardware, servers, and connections that need to be set up to install the database. A logical schema is the structure of the data itself. This is what programmers, database administrators, and end users will be working with.

Below is a graphic of the schema, showing physical and logical. We will consider a database that holds information about music such as albums, artists, and reviews. To keep it simple, the schema snapshot below shows just one of the tables.

Database schema physical logical

Designing a Schema

What other types of data will we store in our music database? We'll want a table for artist data, album data, genres, and reviews. A true music database will be much larger, but let's keep things simple. Also, at one point we will be normalizing the data, that is reducing redundant data among the tables.

In order to keep tables and fields organized, we'll prefix all table names with tbl. Therefore, we can come up with the following tables:

Table Name Primary Key(s) Other fields
tblArtist artistID artistName, artistCountry
tblAlbum albumID artistID, albumTitle, releaseDate, genreID
tblReviews reviewID albumID, reviewText
tblGenres genreID genre

As you can see, we have all of the primary keys organized before the other fields. We have good information, but so far, no logical schema or blueprint. These are just table names. Let's get to building a blueprint. Remember, at this point, we are NOT dealing with actual data. Don't start populating data until you design the schema! Design the schema first and make sure it will meet your needs. It is actually much harder to make changes to a database structure once you start populating the data.

Map it Out

The best way to design a schema is to map out your data in a graphical format. You may have an idea of how the data is to be structured and even have some notes or requirements, but it really helps to see a visual representation of the data. Database tools like Microsoft Access provide tools to set the relationship between tables. This is done BEFORE any data is entered.

Let's look at our music database, with all of the tables displayed. This view is from Microsoft Access, but you could use Visio, other graphing charts or even tools in other database management systems. Right now, it's just a display of the tables. Primary keys are in bold. Again, no data is entered yet.

Database tables

To unlock this lesson you must be a Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use

Become a member and start learning now.
Become a Member  Back
What teachers are saying about
Try it now

Earning College Credit

Did you know… We have over 220 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Used by over 30 million students worldwide
Create an account