{"id":4007,"date":"2024-09-10T09:00:38","date_gmt":"2024-09-10T01:00:38","guid":{"rendered":"https:\/\/five.co\/?p=4007"},"modified":"2024-09-20T16:07:11","modified_gmt":"2024-09-20T08:07:11","slug":"creating-tables","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/creating-tables\/","title":{"rendered":"Database Design: Creating Tables"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Database Design &#8211; Part 1: Creating 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 a database table in Five. Tables are what a MySQL database is made of. Without tables, there is no database. Inside Five we do not need to write complicated <code>CREATE TABLE<\/code> commands. Instead, we can create an entire database in point and click.<\/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 Tables<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To create a table 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 (empty) application ready in Five. If you are entirely new to Five and this is your first time using Five, <a href=\"https:\/\/www.youtube.com\/watch?v=hyQM_0AVvv8\" target=\"_blank\" rel=\"noreferrer noopener nofollow\">make sure to watch this video to learn how to create an application.<\/a><\/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. Add as many fields as required.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Important: Do Not Add a Primary Key<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Five&#8217;s Table Wizard will automatically add a primary key to your table. <strong><span style=\"text-decoration: underline;\">Do not<\/span><\/strong> add a primary key field to your table manually. We will show you how to view the primary key in a second. For now, just remember that you do not need to add the primary key!<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">6. Once you have added all your fields, make the appropriate selections for each one of them. Select a <strong>Data Type, <\/strong>whether the field is <strong>Required<\/strong>, its <strong>Size<\/strong>, and its <strong><a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/display-types\/\">Default Display Type<\/a>.<\/strong> If you are dealing with a text field, we recommend a default <strong>Size<\/strong> of 100.<br>7. 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 database table for 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: Data Types vs Display Types<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">It&#8217;s important not to confuse <em>display types <\/em>with <em>data types.<\/em> <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong><a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/display-types\/\">Display types<\/a><\/strong> define how a form field is displayed to your end-users in the application. They only become relevant once you start building a user interface for your database.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Data types<\/strong>, on the other hand, are on the database level and define how the database stores the data. The data type is a guideline for your SQL database to understand what type of data is expected inside each database column. it also identifies how SQL will interact with the stored data. In Five, data types are defined on the table level.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Five supports all common SQL data types: text (string), binary (for images), boolean, integer, float, memo, password, etc.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Make sure to select the right data type for your fields, especially if you wish to import data from a CSV file into your database, for example. <em>Data <\/em>and <em>data type<\/em> mismatches are very common errors and can be avoided by making sure they are aligned.<\/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 Table<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">To view your database table, 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>looks like 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 every table&#8217;s first field is its primary key. This primary key was automatically created by Five&#8217;s <strong>Table Wizard. <\/strong>We did not have to add this key manually.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Five&#8217;s <strong>Database Modeler <\/strong>is more than just a visual representation of your database. You can make changes to your database table from here. We prefer the <strong>Table Wizard<\/strong>, however, because it is even faster and easier.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Database Design: Practical Tips<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">If you are new to relational database design, the steps we just covered can feel a little bit overwhelming. Luckily, Five follows standard SQL conventions, meaning you can benefit from SQL learning resources on the web to learn more about database design. W3 School, for example, is a popular website for learning SQL. Of course, you can also visit <a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/build-a-database-from-scratch-in-minutes\/\">our website to learn core database concepts.<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In its simplest way, a database is a place that stores your data (almost like an Excel spreadsheet), however with many advantages over a flat spreadsheet. First and foremost, databases such as MySQL are <strong>relational <\/strong>databases, meaning a database can (and usually does) have more than one table. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Five makes it easy to create relationships between tables. In our next post, we will learn how to <a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/table-relationships\/\">create these table relationships<\/a> in 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\">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\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Database Design &#8211; Part 1: Creating 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":4012,"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-4007","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-Creating-Tables.jpg",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Creating-Tables-150x150.jpg",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Creating-Tables-300x169.jpg",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Creating-Tables-768x432.jpg",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Creating-Tables-1024x576.jpg",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Creating-Tables.jpg",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/09\/Five.Co-Database-Design-Creating-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 1: Creating 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\/4007","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=4007"}],"version-history":[{"count":0,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4007\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/4012"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=4007"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=4007"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=4007"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}