{"id":4081,"date":"2024-09-10T09:05:41","date_gmt":"2024-09-10T01:05:41","guid":{"rendered":"https:\/\/five.co\/?p=4081"},"modified":"2024-09-20T16:07:14","modified_gmt":"2024-09-20T08:07:14","slug":"junction-tables","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/junction-tables\/","title":{"rendered":"Database Design: Junction Tables"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Database Design &#8211; Part 3: Junction Tables<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Hi and welcome to Five&#8217;s <em><strong>Database Design<\/strong><\/em> series. This series of blog posts teaches you how to create a MySQL database from scratch in Five. From adding table fields to creating many-to-many relationships and importing data &#8211; we cover everything you need to know to build the perfect database.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This blog post explains how to create relationships between database tables in Five. Five lets you develop a MySQL database, one of the world&#8217;s most popular relational database management systems (RDBMS). MySQL stores data in related tables. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Understanding how to create these relationships is a critical aspect of database design. Inside Five we do not need to write complicated SQL commands to create relationships, but can do so entirely in point and click using the <strong>Table Wizard.<\/strong><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">About Five: More Than Just a Database<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Five is a rapid application development environment for building database-driven web applications. Every application developed in Five has its own, fully customizable MySQL database. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In Five, you can create your database like in MySQL Workbench, with one important difference. You don&#8217;t need to write SQL commands to do so.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Five gives you the power of a database GUI in an easy-to-use and intuitive web application. You can create tables, assign data types, define table relationships, and import or query data. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Beyond its database features, Five lets you develop an entire web application on your database. Five makes it easy to build a modern web app with forms, charts, dashboards, and PDF reports that are all connected to your database. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Unlike traditional SQL tools, which only serve as a graphical user interface for a database (but are usually not very user-friendly), Five allows you to <strong>build and launch a web interface connected to a database<\/strong>. This makes Five ideal for rapidly creating internal databases to store data about products, inventory, business partners, to-do lists, members, suppliers, or other data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To view Five&#8217;s web interface for databases, visit our <a href=\"https:\/\/default-customerdatabase-tryfive.5au.co\/\" target=\"_blank\" rel=\"noreferrer noopener nofollow\">customer database sample application<\/a> here.<\/p>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\">\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(18px + 0.390625vw);\"><strong>Create and Launch a Database-Driven Web App<\/strong><br><span style=\"font-size: 14pt;\">Rapidly build and deploy a MySQL DB today<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Instant Access<\/strong><\/button><br><\/a><\/p>\n<hr style=\"height: 5px;\"><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Five&#8217;s Database Design Tools: The Table Wizard<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Five&#8217;s <strong>Table Wizard<\/strong> is the quickest and easiest way to create a MySQL database. It is a point-and-click interface for creating MySQL database tables and defining relationships.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When creating a new database, we often start by mapping out our tables, fields, and relationships in pen and paper. Which table should store what data? What is each field&#8217;s data type? Are we dealing with strings, floats, integers or booleans? What relationships do I need to create? What about junction tables? <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Equipped with a database model or entity-relationship diagram, we can quickly convert our visual database representation into an actual database using Five.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s learn how to use the <strong>Table Wizard to create a many-to-many relationship by using a junction table <\/strong>in this blog post.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Using Five&#8217;s Table Wizard to Create a Junction Table<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To create a junction table or many-to-many relationship between using Five&#8217;s Table Wizard, follow these steps:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Prerequisites:<\/strong><br>Have at least one application with <strong>two <\/strong>existing database tables that are supposed to be connected by a many-to-many relationship ready in Five.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Explainer: Junction Tables and Many-to-Many Relationships<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">To create a <a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/how-to-create-many-to-many-relationships-in-sql\/\">many-to-many relationship<\/a>, we need to have a total of three tables. For example, let&#8217;s say our database stores information about events and participants. Each event can have many participants. Each participant can go to many events. Here is what our Entity-Relationship Diagram should look like:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img width=\"1024\" height=\"350\" src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-66-1024x350.png\" alt=\"\" class=\"wp-image-4082\" srcset=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-66-1024x350.png 1024w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-66-300x102.png 300w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-66-768x262.png 768w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-66-1536x525.png 1536w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-66.png 2000w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">The <strong>EventParticipant <\/strong>table is a junction table that stores information about events and participants, allowing us to keep track of (many) participants at (many) events. For more details on why this table is required, <a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/how-to-create-many-to-many-relationships-in-sql\/\">please refer to our blog post on many-to-many relationships here<\/a>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">Now, let&#8217;s create the junction table in Five, using the <strong>Table Wizard. <\/strong>As we have already explained <a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/creating-tables\/\">how to create tables<\/a>, we will only explain how to create the junction table.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">1. In Five, select your application and click <strong>Manage.<\/strong><br>2. Now go to <strong>Data &gt; Table Wizard<\/strong>.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img width=\"1024\" height=\"468\" src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-59-1024x468.png\" alt=\"\" class=\"wp-image-4008\" srcset=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-59-1024x468.png 1024w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-59-300x137.png 300w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-59-768x351.png 768w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-59.png 1244w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><figcaption class=\"wp-element-caption\"><em>The <strong>Table Wizard <\/strong>can be accessed from <strong>Data &gt; Table Wizard<\/strong><\/em><\/figcaption><\/figure>\n<\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">3. Give your database table a name by filling in the <strong>Name*<\/strong> field. Usually, junction tables should always be named after the two tables they are joining, such as <strong>EventParticipant<\/strong>.  <br>4. Do <em>not <\/em>add any fields. Instead, click the small next <strong>&gt; Arrow<\/strong>, shown here:<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img width=\"1024\" height=\"533\" src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-62-1024x533.png\" alt=\"\" class=\"wp-image-4014\" srcset=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-62-1024x533.png 1024w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-62-300x156.png 300w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-62-768x400.png 768w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-62.png 1215w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><figcaption class=\"wp-element-caption\"><em>Click <strong>Next<\/strong> to<\/em> <em>create table relationships<\/em><\/figcaption><\/figure>\n<\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">7. Click the <strong>Plus<\/strong> button in the <strong>Relationships <\/strong>section <strong>twice<\/strong>.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img width=\"1019\" height=\"753\" src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-63.png\" alt=\"\" class=\"wp-image-4015\" srcset=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-63.png 1019w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-63-300x222.png 300w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-63-768x568.png 768w\" sizes=\"(max-width: 1019px) 100vw, 1019px\" \/><figcaption class=\"wp-element-caption\"><em>Relationships are created by clicking the <strong>Plus<\/strong> button<\/em><\/figcaption><\/figure>\n<\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">8. From the dropdown field, select the <strong>first <\/strong>related table. Five will create a one-to-many relationship between the table you are creating and the table you are selecting.<br>9. Repeat step 8 for the <strong>second<\/strong> related table. Again, Five will create a one-to-many relationship between the table you are creating and the table you are selecting.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img width=\"1022\" height=\"755\" src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-64.png\" alt=\"\" class=\"wp-image-4016\" srcset=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-64.png 1022w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-64-300x222.png 300w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-64-768x567.png 768w\" sizes=\"(max-width: 1022px) 100vw, 1022px\" \/><figcaption class=\"wp-element-caption\"><em>Select a related table from the dropdown field<\/em><\/figcaption><\/figure>\n<\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">9. Last, click the <strong>Tick \u2714\ufe0f Button <\/strong>to save. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Done! We have just created our first junction table with a many-to-many relationship in Five&#8217;s integrated MySQL database.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Viewing Your Database Tables and Relationships<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">As before, you can view your database tables and relationships, by clicking on <strong>Data &gt; Database Modeler.<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here is what you should see inside the <strong>Database Modeler<\/strong>. Your junction table connects your database tables, and we can now successfully associate participants and events in a many-to-many relationship. The <strong>EventParticipant <\/strong>table holds both the <strong>EventKey <\/strong>and the <strong>ParticipantKey<\/strong> as foreign keys, and, as a third field, has its primary key. <\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img width=\"1024\" height=\"350\" src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-66-1024x350.png\" alt=\"\" class=\"wp-image-4082\" srcset=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-66-1024x350.png 1024w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-66-300x102.png 300w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-66-768x262.png 768w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-66-1536x525.png 1536w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-66.png 2000w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Junction Tables and Many-to-Many Relationships: Practical Tips<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Now that we have created a many-to-many relationship, you can see the advantages of using Five&#8217;s <strong>Table Wizard. <\/strong>With the Table Wizard, we can easily create tables, assign data types, and create relationships.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">However, to do so, Five needs to be in charge of primary and foreign keys. So as mentioned before: when using Five and the <strong>Table Wizard,<\/strong> do not create primary keys. Five handles these for you. <\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Finding Help<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Have more questions? <a href=\"https:\/\/five.org\" rel=\"nofollow noopener\" target=\"_blank\">Join our user community<\/a> to connect with others and get answers.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Continue Reading About Database Design<\/h2>\n\n\n\n<div class=\"wp-block-group\"><div class=\"wp-block-group__inner-container is-layout-constrained wp-block-group-is-layout-constrained\">\n<p class=\"wp-block-paragraph\">Discover all articles of our <em>Database Design<\/em> series here. <\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/creating-tables\/\" target=\"_blank\" data-type=\"link\" data-id=\"https:\/\/preview-php85.dzine.org\/five\/blog\/creating-tables\/\" rel=\"noreferrer noopener\">Creating Tables<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/table-relationships\/\" target=\"_blank\" data-type=\"link\" data-id=\"https:\/\/preview-php85.dzine.org\/five\/blog\/table-relationships\/\" rel=\"noreferrer noopener\">Creating Relationships<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/junction-tables\/\" target=\"_blank\" rel=\"noreferrer noopener\">Junction Tables<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/import-csv-into-mysql\/\">Importing Data<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/external-databases\/\" target=\"_blank\" data-type=\"link\" data-id=\"https:\/\/preview-php85.dzine.org\/five\/blog\/external-databases\/\" rel=\"noreferrer noopener\">Connecting External Databases<\/a><\/li>\n<\/ol>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Database Design &#8211; Part 3: Junction Tables Hi and welcome to Five&#8217;s Database Design series. This series of blog posts teaches you how to create a MySQL database from scratch in Five. From adding table fields to creating many-to-many relationships and importing data &#8211; we cover everything you need to know to build the perfect [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":4083,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21,49],"tags":[],"class_list":["post-4081","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-how-to"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Junction-Tables.jpg",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Junction-Tables-150x150.jpg",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Junction-Tables-300x169.jpg",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Junction-Tables-768x432.jpg",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Junction-Tables-1024x576.jpg",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Junction-Tables.jpg",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Junction-Tables.jpg",1280,720,false]},"uagb_author_info":{"display_name":"Dominik Keller","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed3_my\/"},"uagb_comment_info":0,"uagb_excerpt":"Database Design &#8211; Part 3: Junction Tables Hi and welcome to Five&#8217;s Database Design series. This series of blog posts teaches you how to create a MySQL database from scratch in Five. From adding table fields to creating many-to-many relationships and importing data &#8211; we cover everything you need to know to build the perfect&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4081","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=4081"}],"version-history":[{"count":0,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4081\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/4083"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=4081"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=4081"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=4081"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}