{"id":166,"date":"2024-11-20T15:22:43","date_gmt":"2024-11-20T08:22:43","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=166"},"modified":"2024-11-20T15:22:44","modified_gmt":"2024-11-20T08:22:44","slug":"database-design","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/database-design\/","title":{"rendered":"Database Design"},"content":{"rendered":"\n<p><strong>Summary:<\/strong> In this tutorial, you&#8217;ll learn how to design a PostgreSQL database for your application.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='business-requirements'>Business Requirements <a href=\"#business-requirements\" class=\"anchor\" id=\"business-requirements\" title=\"Anchor for Business Requirements\">#<\/a><\/h2>\n\n\n\n<p>We&#8217;ll design a database for a simple inventory management system.<\/p>\n\n\n\n<p>Let&#8217;s start with the business requirements:<\/p>\n\n\n\n<p>&#8220;Our inventory management system empowers warehouse users to efficiently manage inventory from multiple warehouses. <\/p>\n\n\n\n<p>It streamlines product management, enabling users to handle products across tags, categories, and brands effectively. <\/p>\n\n\n\n<p>Additionally, the system records all goods receipt\/issue transactions, ensuring smooth and efficient inventory management.&#8221;<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='important-questions'>Important Questions <a href=\"#important-questions\" class=\"anchor\" id=\"important-questions\" title=\"Anchor for Important Questions\">#<\/a><\/h2>\n\n\n\n<p>To design a database for the inventory management system, you need to ask the following important questions:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>What tables should the database have?<\/li>\n\n\n\n<li>What are the relationships between these tables?<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id='identifying-tables'>Identifying Tables <a href=\"#identifying-tables\" class=\"anchor\" id=\"identifying-tables\" title=\"Anchor for Identifying Tables\">#<\/a><\/h2>\n\n\n\n<p>The inventory system allows multiple users to access and perform inventory management tasks, so the database should have a table called <code>users<\/code>.<\/p>\n\n\n\n<p>Each user may have a profile to store additional information like first name, last name, and phone. Therefore, the database should have a table called <code>profiles<\/code>.<\/p>\n\n\n\n<p>The system needs to manage multiple warehouses. To track these warehouses, we can create a <code>warehouses<\/code> table.<\/p>\n\n\n\n<p>The inventory system manages multiple products across tags, brands, and categories, so the database should have four more tables: <code>tags<\/code>, <code>brands<\/code>, <code>categories<\/code>, and <code>products<\/code>.<\/p>\n\n\n\n<p>When warehouse users receive or issue products, they record the transactions. The database should have a <code>transactions<\/code> table to record these transactions.<\/p>\n\n\n\n<p>To provide a more granular view of product quantities across warehouses, we can create a table called <code>inventories<\/code>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='table-relationships'>Table Relationships <a href=\"#table-relationships\" class=\"anchor\" id=\"table-relationships\" title=\"Anchor for Table Relationships\">#<\/a><\/h2>\n\n\n\n<p>Let&#8217;s find the relationship between tables.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='one-to-one-relationships'>One-to-One Relationships <a href=\"#one-to-one-relationships\" class=\"anchor\" id=\"one-to-one-relationships\" title=\"Anchor for One-to-One Relationships\">#<\/a><\/h3>\n\n\n\n<p>Each user has a profile, and a profile is linked to a user. The relationship between the user and profile is one-to-one.<\/p>\n\n\n\n<p>In PostgreSQL, each row in the users table has a corresponding row in the <code>profiles<\/code> table.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='one-to-many-relationships'>One-to-Many Relationships <a href=\"#one-to-many-relationships\" class=\"anchor\" id=\"one-to-many-relationships\" title=\"Anchor for One-to-Many Relationships\">#<\/a><\/h3>\n\n\n\n<p>Each brand has many products, and a product belongs to a brand. The relationship between brand and product is one-to-many.<\/p>\n\n\n\n<p>Similarly, each category has many products, and each product belongs to a category. The relationship between category and product is one-to-many.<\/p>\n\n\n\n<p>In PostgreSQL, one row in the categories table is associated with multiple rows in the products table. Likewise, one row in the brands table is related to multiple rows in the products table.<\/p>\n\n\n\n<p>A user may record zero or more transactions, and a transaction is recorded by one and only one user. So, the relationship between the user and the transaction is one-to-many.<\/p>\n\n\n\n<p>A warehouse may have multiple transactions, and a transaction belongs to one and only one warehouse. The relationship between the warehouse and the transaction is one-to-many.<\/p>\n\n\n\n<p>A product may have multiple transactions, and a transaction belongs to one product. The relationship between product and transaction is one-to-many.<\/p>\n\n\n\n<p>A category can have subcategories. The relationship between category and subcategories is one-to-many. We can use the same categories table to store both categories and subcategories.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id='many-to-many-relationships'>Many-to-Many Relationships <a href=\"#many-to-many-relationships\" class=\"anchor\" id=\"many-to-many-relationships\" title=\"Anchor for Many-to-Many Relationships\">#<\/a><\/h3>\n\n\n\n<p>A product has one or more tags, and one tag has many products. The relationship between product and tag is many-to-many.<\/p>\n\n\n\n<p>In PostgreSQL, we can model the many-to-many relationship by using two one-to-many relationships by creating a link table called <code>product_tags<\/code>.<\/p>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful ?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"166\"\n\t\t\t\tdata-post-url=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/database-design\/\"\n\t\t\t\tdata-post-title=\"Database Design\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"166\"\n\t\t\t\tdata-post-url=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/database-design\/\"\n\t\t\t\tdata-post-title=\"Database Design\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Summary: In this tutorial, you&#8217;ll learn how to design a PostgreSQL database for your application. Business Requirements # We&#8217;ll design a database for a simple inventory management system. Let&#8217;s start with the business requirements: &#8220;Our inventory management system empowers warehouse users to efficiently manage inventory from multiple warehouses. It streamlines product management, enabling users to [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":13,"menu_order":11,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-166","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Database Design<\/title>\n<meta name=\"description\" content=\"In this tutorial, you&#039;ll learn how to design a PostgreSQL database for your application.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/database-design\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Database Design\" \/>\n<meta property=\"og:description\" content=\"In this tutorial, you&#039;ll learn how to design a PostgreSQL database for your application.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/database-design\/\" \/>\n<meta property=\"og:site_name\" content=\"PostgreSQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2024-11-20T08:22:44+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/database-design\\\/\",\"url\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/database-design\\\/\",\"name\":\"Database Design\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/#website\"},\"datePublished\":\"2024-11-20T08:22:43+00:00\",\"dateModified\":\"2024-11-20T08:22:44+00:00\",\"description\":\"In this tutorial, you'll learn how to design a PostgreSQL database for your application.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/database-design\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/database-design\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/database-design\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.pgtutorial.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL Tutorial\",\"item\":\"https:\\\/\\\/www.pgtutorial.com\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Database Design\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/#website\",\"url\":\"https:\\\/\\\/www.pgtutorial.com\\\/\",\"name\":\"PostgreSQL Tutorial\",\"description\":\"Learn PostgreSQL from Scratch\",\"alternateName\":\"PostgreSQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.pgtutorial.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Database Design","description":"In this tutorial, you'll learn how to design a PostgreSQL database for your application.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/database-design\/","og_locale":"en_US","og_type":"article","og_title":"Database Design","og_description":"In this tutorial, you'll learn how to design a PostgreSQL database for your application.","og_url":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/database-design\/","og_site_name":"PostgreSQL Tutorial","article_modified_time":"2024-11-20T08:22:44+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/database-design\/","url":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/database-design\/","name":"Database Design","isPartOf":{"@id":"https:\/\/www.pgtutorial.com\/#website"},"datePublished":"2024-11-20T08:22:43+00:00","dateModified":"2024-11-20T08:22:44+00:00","description":"In this tutorial, you'll learn how to design a PostgreSQL database for your application.","breadcrumb":{"@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/database-design\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pgtutorial.com\/postgresql-tutorial\/database-design\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/database-design\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pgtutorial.com\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL Tutorial","item":"https:\/\/www.pgtutorial.com\/"},{"@type":"ListItem","position":3,"name":"Database Design"}]},{"@type":"WebSite","@id":"https:\/\/www.pgtutorial.com\/#website","url":"https:\/\/www.pgtutorial.com\/","name":"PostgreSQL Tutorial","description":"Learn PostgreSQL from Scratch","alternateName":"PostgreSQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pgtutorial.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/166","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/comments?post=166"}],"version-history":[{"count":3,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/166\/revisions"}],"predecessor-version":[{"id":169,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/166\/revisions\/169"}],"up":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/13"}],"wp:attachment":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/media?parent=166"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}