{"id":4013,"date":"2024-09-10T09:03:26","date_gmt":"2024-09-10T01:03:26","guid":{"rendered":"https:\/\/five.co\/?p=4013"},"modified":"2024-09-20T16:07:12","modified_gmt":"2024-09-20T08:07:12","slug":"table-relationships","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/table-relationships\/","title":{"rendered":"Database Design: Table Relationships"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Database Design &#8211; Part 2: Table Relationships<\/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 <\/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 Relationships<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To create a relationship between two tables 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 an existing database table ready in Five.<\/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, table names should always be singular, using PascalCasing, such as <strong>CustomerName. <\/strong><br>4. Next, click the <strong>Plus <\/strong>button on the right side of the screen to add fields to your database, as shown here:<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img width=\"1287\" height=\"737\" src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/data-type.gif\" alt=\"\" class=\"wp-image-4010\"\/><figcaption class=\"wp-element-caption\"><em>Add fields to your database 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\">5. Keep clicking the <strong>Plus <\/strong>button to add more fields to your table. As before, do not add a primary key to your table. This is done automatically by the <strong>Table Wizard.<\/strong><br>6. Once you have added all your fields to your table, 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.<\/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 related table. 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 related table 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<h3 class=\"wp-block-heading\">Explainer: Table Relationships<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">When you select a table in the <strong>Relationships <\/strong>section, Five creates a one-to-many relationship. For example, let&#8217;s assume we have two tables: Gym and Member. We know that one gym can have many members, so we want a one-to-many relationship between the Gym and the Member table.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To do so, we <strong>first <\/strong>create our Gym table, as we did in <a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/creating-tables\/\">part 1 of database design<\/a>, i.e. we create the table without any relationships. <strong>Second<\/strong>, we create the Member table as described above. For the Member table, we select the Gym table as a related table.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This will add the Gym table&#8217;s primary key as a foreign key to the Member table and create the one-to-many relationship that we need!<\/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\">To view your database tables and relationships, follow these steps:<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">1. Click <strong>Data &gt; Database Modeler.<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here is what the <strong>Database Modeler <\/strong>shows for our <a href=\"https:\/\/default-customerdatabase-tryfive.5au.co\/\" rel=\"nofollow noopener\" target=\"_blank\">customer database sample application<\/a>.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><img width=\"1024\" height=\"743\" src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-61-1024x743.png\" alt=\"\" class=\"wp-image-4011\" srcset=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-61-1024x743.png 1024w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-61-300x218.png 300w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-61-768x557.png 768w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/image-61.png 1530w\" sizes=\"(max-width: 1024px) 100vw, 1024px\" \/><figcaption class=\"wp-element-caption\"><em>Five&#8217;s Database Modeler<\/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\">Note how the tables are connected by thin lines that indicate the type of relationship. In Five, zoom in on one of these lines. Each line&#8217;s ends are either a single line or multiple lines (&#8220;crow&#8217;s feet&#8221;), indicating how the two tables are related. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">You can now also see that some tables contain foreign keys. In the <strong>Database Modeler <\/strong>image above, look at the relationship between the Categories and Products tables. One category (&#8220;electronics&#8221;) can have many products (&#8220;phones&#8221;, &#8220;TVs&#8221;, &#8220;laptops&#8221;). This is why the Products table contains the CategoriesKey. <\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Table Relationships: Practical Tips<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Almost every database consists of several related tables. Very few applications can do with just a single, flat table. Often, however, we are not dealing with a simple <strong>one-to-many<\/strong> relationship, but with <strong>many-to-many<\/strong> relationships. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In the example of gyms and members above, we could imagine that someone is a member of multiple gyms at once: one gym for spinning, another for strength workouts. Our database design above cannot accommodate this scenario, as we only created a one-to-many relationship (one gym can have many members. But one member cannot have many gyms).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s learn how to change this in our next blog post on many-to-many relationships.<\/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 2: Table Relationships 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":4017,"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-4013","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-Table-Relationships.jpg",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Table-Relationships-150x150.jpg",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Table-Relationships-300x169.jpg",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Table-Relationships-768x432.jpg",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Table-Relationships-1024x576.jpg",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Table-Relationships.jpg",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Table-Relationships.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 2: Table Relationships 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\/4013","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=4013"}],"version-history":[{"count":0,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4013\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/4017"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=4013"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=4013"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=4013"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}