Relational Database Design

<!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <meta name="keywords" content="relational database design, concepts of database management systems, fundamentals, database management system, web based database, home, create, design"> <meta name="Description" content="Learn about relational database design with this database tutorial."> <link rel="canonical" href="https://www.quackit.com/database/tutorial/relational_database_design.cfm"> <title>Relational Database Design</title> <script src="//ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script> <link rel="shortcut icon" href="/pix/favicon96.png"> <link rel="apple-touch-icon" href="/pix/apple-touch-icon.png"> <link rel="preconnect" href="https://fonts.googleapis.com"> <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin> <link href="https://fonts.googleapis.com/css2?family=Lato:ital,wght@0,400;0,700;1,400&display=swap" rel="stylesheet"> <link href="/common/css/master.45.min.css" rel="stylesheet"> <script async src="https://cdn.fuseplatform.net/publift/tags/2/3499/fuse.js"></script>  <script async src="https://www.googletagmanager.com/gtag/js?id=G-Q3H025ZKLN"></script> <script> window.dataLayer = window.dataLayer || []; function gtag(){dataLayer.push(arguments);} gtag('js', new Date()); gtag('config', 'G-Q3H025ZKLN'); </script> </head> <body> <header class="site-header"> <div class="site-header-base"> <div class="site-logo"> <a title="Quackit Homepage" target="_top" href="/"><img src="/pix/quackit_logo_watermark.png" width="87" height="33" alt="Quackit Logo"></a> </div> <button id="site-nav-toggler" class="site-nav-toggler" aria-expanded="false" aria-controls="site-nav"> <span class="sr-only">Toggle navigation</span> ☰ </button> </div> <nav id="site-nav" class="site-nav"> <div class="site-links"> <ul> <li><a href="/"><i class="fa fa-home"></i> <span class="sr-only">Home</span></a></li> <li><a href="/html/">HTML</a></li> <li><a href="/css/">CSS</a></li> <li><a href="/scripting/">Scripting</a></li> <li><a href="/database/">Database</a></li> </ul> </div> <div class="site-search-top"> <form action="/search/" id="cse-search-box-bottom" class="site-search"> <div> <input type="hidden" name="cx" value="partner-pub-6331358926293806:98x0fk-bbgi"> <input type="hidden" name="cof" value="FORID:10"> <input type="hidden" name="ie" value="ISO-8859-1"> <input type="text" name="q" size="20" class="site-search-input"> <button type="submit" name="sa" class="site-search-button"><i class="fa fa-search"></i></button> </div> </form> </div> </nav> </header> <div class="main"> <article class="content"> <h1 class="page-title">Relational Database Design</h1> <div class="ad ad-top">  <div data-fuse="23059883623"></div> </div> <ul class="pager"> <li><a href="/database/tutorial/querying_a_database.cfm"><i class="fa fa-long-arrow-left" aria-hidden="true"></i> Querying a Database</a></li> <li><a href="/database/tutorial/nosql_databases.cfm">NoSQL Databases <i class="fa fa-long-arrow-right" aria-hidden="true"></i></a></li> </ul> <p class="lead">This page provides an overview of data model used by relational database management systems.</p> <p>Most popular database management systems are <em>relational</em> systems, and are usually referred to as Relational Database Management Systems (RDBMS). What this means is that their databases can contain multiple tables, some (or all) of which are related to each other. </p> <div class="ad">  <div data-fuse="23059883629"></div> </div> <h2>Relationships</h2> <p>When two or more tables contain related data, they are said to have a <dfn>relationship</dfn>.</p> <p>Relationships are something you design. They don't happen by accident. You actually design your database in a way that determines which tables will have a relationship.</p> <ul class="steps"> <li> <figure> <a target="_blank" data-lightbox="database_tutorial" href="/pix/database/tutorial/relational_database_design_1.png"><img src="/pix/database/tutorial/relational_database_design_1.png" class="screenshot steps" alt="Screenshot of two related tables."></a> <figcaption> <h3>Example of a Relationship</h3> <p>This screenshot shows an example of a relationship. </p> <p>The <samp>Artists</samp> and <samp>Albums</samp> tables are related. This is because the <samp>Albums</samp> table has an <samp>ArtistId</samp> column that corresponds to the <samp>ArtistId</samp> in the <samp>Albums</samp> table. </p> <p>In this case, the <samp>Artists</samp> table is the parent table (or primary table), and the <samp>Albums</samp> table is the child table.</p> <p>Any value stored in the <samp>Albums.ArtistId</samp> column must correspond with a value in the <samp>Artists.ArtistId</samp> column.</p> <p>Doing this enables us to look up the artist's name for any given album.</p> </figcaption> </figure> </li> </ul> <h2>How Does This Work?</h2> <p>The following screenshots demonstrate the data involved in the above relationship.</p> <ul class="steps"> <li> <figure> <a target="_blank" data-lightbox="database_tutorial" href="/pix/database/tutorial/relational_database_design_2.png"><img src="/pix/database/tutorial/relational_database_design_2.png" class="screenshot steps" alt="Screenshot of the Artists table."></a> <figcaption> <h3>The <samp>Artists</samp> Table</h3> <p>Firstly, in the <samp>Artists</samp> table, each record has a primary key. A <dfn>primary key</dfn> is a column that stores a unique identifier for for each record.</p> <p>The value of this unique identifier can be as simple as an incrementing number. So, in our <samp>Artists</samp> table, the first record has a value of <samp>1</samp>, the second record has a value of <samp>2</samp>, and so on.</p> </figcaption> </figure> </li> <li> <figure> <a target="_blank" data-lightbox="database_tutorial" href="/pix/database/tutorial/relational_database_design_3.png"><img src="/pix/database/tutorial/relational_database_design_3.png" class="screenshot steps" alt="Screenshot of the Albums table."></a> <figcaption> <h3>The <samp>Albums</samp> Table</h3> <p>Secondly, when entering each album into the <samp>Albums</samp> table, instead of writing out the full artist's name in that table, we only need to add the artist's unique identifier. In this case, the unique identifier is a number, so we enter this number into the <samp>ArtistId</samp> column of the <samp>Albums</samp> table.</p> <p>This column is referred to as a <dfn>foreign key</dfn>, because it references data from the primary key of another table.</p> <p>So we can see that the first album belongs to artist <samp>3</samp>. If we look at the <samp>Artists</samp> table, we can see that artist <samp>3</samp> is <samp>Iron Maiden</samp>.</p> </figcaption> </figure> </li> </ul> <ul class="pager"> <li><a href="/database/tutorial/querying_a_database.cfm"><i class="fa fa-long-arrow-left" aria-hidden="true"></i> Querying a Database</a></li> <li><a href="/database/tutorial/nosql_databases.cfm">NoSQL Databases <i class="fa fa-long-arrow-right" aria-hidden="true"></i></a></li> </ul> </article> <div class="sidebar"> <nav> <ul> <li> <h3><a href="/database/tutorial/"><i class="fa fa-database"></i> Database Tutorial</a></h3> <ul> <li><a href="/database/tutorial/what_is_a_database.cfm">What is a Database?</a></li> <li><a href="/database/tutorial/database_management_systems.cfm">Database Management Systems</a></li> <li><a href="/database/tutorial/creating_a_database.cfm">Creating a Database</a></li> <li><a href="/database/tutorial/about_database_tables.cfm">About Database Tables</a></li> <li><a href="/database/tutorial/creating_database_tables.cfm">Creating Database Tables</a></li> <li><a href="/database/tutorial/adding_data_to_database.cfm">Adding Data to a Database</a></li> <li><a href="/database/tutorial/querying_a_database.cfm">Querying a Database</a></li> <li><a href="/database/tutorial/relational_database_design.cfm">Relational Database Design</a></li> <li><a href="/database/tutorial/nosql_databases.cfm">NoSQL Databases</a></li> <li><a href="/database/tutorial/database_driven_website.cfm">Database Driven Website</a></li> <li><a href="/database/tutorial/database_summary.cfm">Summary</a></li> </ul> </li> <li> <h3><a href="/database/"><i class="fa fa-database"></i> Other DB Tutorials</a></h3> <ul> <li><a href="/sql/tutorial/" title="Structured Query Language">SQL Tutorial</a></li> <li><a href="/sqlite/tutorial/" title="">SQLite Tutorial</a></li> <li><a href="/mysql/tutorial/" title="Open source DB management system">MySQL Tutorial</a></li> <li><a href="/sql_server/tutorial/" title="Microsoft SQL Server">SQL Server Tutorial</a></li> <li><a href="/microsoft_access/tutorial/" title="Microsoft Access">Access Tutorial</a></li> <li><a href="/mongodb/tutorial/" title="Document-store database for big data.">MongoDB Tutorial</a></li> <li><a href="/neo4j/tutorial/" title="Graph database management system for big data.">Neo4j Tutorial</a></li> </ul> </li> </ul> </nav> <div class="ad ad-left">  <div data-fuse="23059511712"></div> </div> </div> <div class="ads"> <div class="ad ad-right">  <div data-fuse="23059883626"></div> </div> </div> </div> <div class="searchbox-bottom"> <form action="/search/" id="cse-search-box-bottom" class="site-search"> <div> <input type="hidden" name="cx" value="partner-pub-6331358926293806:npmuvy-i8kk"> <input type="hidden" name="cof" value="FORID:10"> <input type="hidden" name="ie" value="ISO-8859-1"> <input type="text" name="q" size="30" class="site-search-input"> <button type="submit" name="sa" class="site-search-button"><i class="fa fa-search"></i></button> </div> </form> <script src="//cse.google.com/cse/brand?form=cse-search-box-bottom&lang=en"></script> </div> <footer> <p class="about"><a href="/"><i class="fa fa-home"></i> Home</a> | <a href="/about.cfm" rel="nofollow">About</a> | <a href="/contact.cfm" rel="nofollow">Contact</a> | <a href="/terms_of_use.cfm" rel="nofollow">Terms of Use</a> | <a href="/privacy_policy.cfm" rel="nofollow">Privacy Policy</a></p> <p>© Copyright 2000 - 2025 Quackit.com  </p> </footer> <script src="/common/js/spectrum/spectrum.js"></script> <script src="/common/js/lightbox2-master/dist/js/lightbox.min.js" charset="utf-8"></script> <script> $(document).ready(function(){ $( "#site-nav-toggler" ).click(function() { $( "#site-nav" ).toggle( "slow" ); }); }); </script> <script> $(function(){var a=window.location.href;$(".sidebar nav a").each(function(){a==this.href&&$(this).closest("li").addClass("selected")})}); </script> </body> </html>

CINXE.COM

Relational Database Design