Skip to main content

2 - Build Database Tables

Last updated 13/12/2023

When an application record is saved in Five, a MySQL database is created and Five automatically establishes the connection with the external database management system (DBMS).

With your Five account running in the Cloud, you can have multiple databases connected to different database management systems.

note
In Five's Desktop version, you cannot have multiple databases for an application.

This documentation will explain how to create and add tables to the MySQL database for the Portfolio application via Five's Table Wizard. In total, five tables will be added. The Portfolio database model below shows the relationships between the tables.

The Sector and StockExchange tables have a one-to-many relationship with the Stock table and the Stock table has a one-to-many relationship with the StockPrice table. At this stage no relationship has been established with the Portfolio table.


Portfolio Base Model Stage One
Figure 1 - Portfolio model stage one

info
When working in the Cloud, you will need to be logged in as a Developer or Control user of Five to have the permissions to build tables. If this is your first time logging in with the Developer (dev) user, please refer to the chapter First Time Logging into Your Cloud Account.

note
The following two steps are only required if you are working in the Cloud, if you are working in Five Desktop version, you will continue using Five as a Standalone user.

1. Log out with your Administrator (admin) account.


Logout button
Figure 2 - Logout button

2. Sign in with your Developer (dev) account.


dev sign in
Figure 3 - dev sign in

3. Click the Manage button in the form app bar.

info
If you are working in the Cloud and you have other saved application records in Five, you will need to select the Portfolio record in the list first to you have access to the Manage button.

Manage Button
Figure 4 - Manage button

4. Click Data in the menu.

5. Click Table Wizard in the sub-menu.


Table Wizard Menu Item
Figure 5 - Table Wizard menu item

Add the Portfolio Table

1. Type Portfolio in the Name field.


Add the Portfolio Table
Figure 6 - Add the Portfolio table

tip
Five automatically creates a Primary Key field and index for your table when you create a table via the Table Wizard!

2. Click the Add Fields button.

3. Type Name in the Name field.

4. Type 255 in the Size field.


Add the Name Field
Figure 7 - Add the Name field

5. Click the Save button in the Table Wizard app bar.


Save button
Figure 8 - Save button

6. Click the Save button in the Table Upgrade window.


Save button
Figure 9 - Save button

info
The Table Wizard may take a few seconds to reload.

tip
Once you have saved a table via our Table Wizard, you will find the table record in the Tables view. To navigate to the Tables view, click Data in the menu followed by clicking Tables in the sub-menu.

Navigate to the Table Record
Figure 10 - Navigate to the Portfolio table record

Add the StockExchange Table

info
If you navigated to the Tables view to see the Portfolio record, you will need to navigate back to the Table Wizard.

1. Click Data in the menu.

2. Click Table Wizard in the sub-menu.


Table Wizard menu item
Figure 11 - Table Wizard menu item

3. Type StockExchange in the Name field.


Add the StockExchange Table
Figure 12 - Add the StockExchange table

4. Click the Add Fields button.

5. Type StockExchangeCode in the Name field.

6. Type 20 in the Size field.


Add the StockExchangeCode Field
Figure 13 - Add the StockExchangeCode field

7. Click the Add Fields button.

8. Type Name in the Name field.

9. Click the Req. checkbox.

10. Type 255 in the Size field.


Add the Name Field
Figure 14 - Add the Name field

11. Click the Save button in the Table Wizard app bar.


Save button
Figure 15 - Save button

12. Click the Save button in the Table Upgrade window.


Save button
Figure 16 - Save button

Add the Sector Table

1. Type Sector in the Name field.


Add the Sector Table
Figure 17 - Add the Sector table

2. Click the Add Fields button.

3. Type Name in the Name field.

4. Type 255 in the Size field.


Add the Name Field
Figure 18 - Add the Name field

5. Click the Save button in the Table Wizard app bar.


Save button
Figure 19 - Save button

6. Click the Save button in the Table Upgrade window.


Save button
Figure 20 - Save button

Add the Stock Table

1. Type Stock in the Name field.


Add the Stock Table
Figure 21 - Add the Stock table

2. Click the Add Fields button.

3. Type StockCode in the Name field.

4. Type 20 in the Size field.


Add the StockCode Field
Figure 22 - Add the StockCode field

5. Click the Add Fields button.

6. Type Name in the Name field.

7. Click the Req. checkbox.

8. Type 255 in the Size field.


Add the Name Field
Figure 23 - Add the Name field

9. Click the Next button.


Next Button
Figure 24 - Next button

10. Click the Add Relationships button.

11. Click the lookup icon in the Table field and select StockExchange.

12. Click the Required checkbox.

tip
Five automatically creates a Foreign Key field and index when you establish a relationship with another table!

Add the StockExchange Relationship
Figure 25 - Add the StockExchange relationship

13. Click the Add Relationships button.

14. Click the lookup icon in the Table field and select Sector.

15. Click the Required checkbox.


Add the Sector Relationship
Figure 26 - Add the Sector relationship

16. Click the Save button in the Table Wizard app bar.


Save button
Figure 27 - Save button

17. Click the Save button in the Table Upgrade window.


Save button
Figure 28 - Save button

Add the StockPrice Table

1. Type StockPrice in the Name field.


Add the StockPrice Table
Figure 29 - Add the StockPrice table

2. Click the Add Fields button.

3. Type PriceDate in the Name field.

tip
Five sets some field definitions for you dependent on the name or data type of the field!

Add the PriceDate Field
Figure 30 - Add the PriceDate field

4. Click the Add Fields button.

5. Type OpenPrice in the Name field.

6. Click the lookup icon in the Data Type field and select Float.


Add the OpenPrice Field
Figure 31 - Add the OpenPrice field

7. Click the Add Fields button.

8. Type HighPrice in the Name field.

9. Click the lookup icon in the Data Type field and select Float.


Add the HighPrice Field
Figure 32 - Add the HighPrice field

10. Click the Add Fields button.

11. Type LowPrice in the Name field.

12. Click the lookup icon in the Data Type field and select Float.


Add the LowPrice Field
Figure 33 - Add the LowPrice field

13. Click the Add Fields button.

14. Type ClosePrice in the Name field.

15. Click the lookup icon in the Data Type field and select Float.


Add the ClosePrice Field
Figure 34 - Add the ClosePrice field

16. Click the Add Fields button.

17. Type Volume in the Name field.

18. Click the lookup icon in the Data Type field and select Integer.


Add the Volume Field
Figure 35 - Add the Volume field

19. Click the Next button.


Next Button
Figure 36 - Next button

20. Click the Add Relationships button.

21. Click the lookup icon in the Table field and select Stock.

22. Click the Required checkbox.


Add the Stock Relationship
Figure 37 - Add the Stock relationship

23. Click the Save button in the Table Wizard app bar.


Save Button
Figure 38 - Save button

24. Click the Save button in the Table Upgrade window.


Save button
Figure 39 - Save button