2 - Build Database Tables

Last updated 8/11/2022

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

Figure 1 - Portfolio Base model stage one

How to Build a Table Using Five's Table Wizard

Navigate to Tables

1. Click the [ Manage button ] in the [ form app bar ].

Figure 1 - Manage button

2. Select [ Tables ] in the [ menu ].

Figure 2 - Tables menu item

Add the Portfolio Table

1. Click the [ New Table Wizard button ] in the [ list app bar ].

2. Type " Portfolio " in the [ Name field ].

Figure 3 - Add the Portfolio table

3. Click the [ Add Fields button ].

tip

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

4. Type " Name " in the [ Name field ].

5. Type " 100 " in the [ Size field ].

Figure 4 - Add the Name field

6. Click the [ Save button ] in the [ Table Wizard app bar ].

Figure 5 - Save the Portfolio table

Add the Exchange Table

1. Click the [ New Table Wizard button ] in the [ list app bar ].

2. Type " Exchange " in the [ Name field ].

3. Click the [ Add Fields button ].

4. Type " ExchangeCode " in the [ Name field ].

5. Type " 20 " in the [ Size field ].

Figure 6 - Add the Exchange table and the ExchangeCode field

6. Click the [ Add Fields button ].

7. Type " Name " in the [ Name field ].

8. Click the [ Req. checkbox ].

9. Type " 100 " in the [ Size field ].

Figure 7 - Add the Name field

10. Click the [ Save button ] in the [ Table Wizard app bar ].

Figure 8 - Save the Exchange table

Add the Sector Table

1. Click the [ New Table Wizard button ] in the [ list app bar ].

2. Type " Sector " in the [ Name field ].

3. Click the [ Add Fields button ].

4. Type " Name " in the [ Name field ].

5. Type " 100 " in the [ Size field ].

Figure 9 - Add the Sector table and the Name field

6. Click the [ Save button ] in the [ Table Wizard app bar ].

Figure 10 - Save the Sector table

Add the Stock Table

1. Click the [ New Table Wizard button ] in the [ list app bar ].

2. Type " Stock " in the [ Name field ].

3. Click the [ Add Fields button ].

4. Type " StockCode " in the [ Name field ].

5. Type " 20 " in the [ Size field ].

Figure 11 - Add the Stock table and the StockCode field

6. Click the [ Add Fields button ].

7. Type " Name " in the [ Name field ].

8. Click the [ Req. checkbox ].

9. Type " 100 " in the [ Size field ].

Figure 12 - Add the Name field

10. Click the [ Next button ].

Figure 13 - Next button

11. Click the [ Add Relationships button ].

12. Click the [ lookup icon ] in the [ Table field ] and select " Exchange ".

13. Click the [ Required checkbox ].

Figure 14 - Add the Exchange relationship

14. Click the [ Add Relationships button ].

tip

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

15. Click the [ lookup icon ] in the [ Table field ] and select " Sector ".

16. Click the [ Required checkbox ].

Figure 15 - Add the Sector relationship

17. Click the [ Save button ] in the [ Table Wizard app bar ].

Figure 16 - Save the Stock table

Add the StockPrice Table

1. Click the [ New Table Wizard button ] in the [ list app bar ].

2. Type " StockPrice " in the [ Name field ].

3. Click the [ Add Fields button ].

4. Type " PriceDate " in the [ Name field ].

Figure 17 - Add the StockPrice table and the PriceDate field

5. Click the [ Add Fields button ].

6. Type " Open " in the [ Name field ].

7. Click the [ lookup icon ] in the [ Data Type field ] and select " Float ".

Figure 18 - Add the Open field

8. Click the [ Add Fields button ].

9. Type " High " in the [ Name field ].

10. Click the [ lookup icon ] in the [ Data Type field ] and select " Float ".

Figure 19 - Add the High field

11. Click the [ Add Fields button ].

12. Type " Low " in the [ Name field ].

13. Click the [ lookup icon ] in the [ Data Type field ] and select " Float ".

Figure 20 - Add the Low field

14. Click the [ Add Fields button ].

15. Type " Close " in the [ Name field ].

16. Click the [ lookup icon ] in the [ Data Type field ] and select " Float ".

Figure 21 - Add the Close field

17. Click the [ Add Fields button ].

18. Type " Volume " in the [ Name field ].

19. Click the [ lookup icon ] in the [ Data Type field ] and select " Integer ".

Figure 22 - Add the Volume field

20. Click the [ Next button ].

Figure 23 - Next button

21. Click the [ Add Relationships button ].

22. Click the [ lookup icon ] in the [ Table field ] and select " Stock ".

23. Click the [ Required checkbox ].

Figure 24 - Add the Stock relationship

24. Click the [ Save button ] in the [ Table Wizard app bar ].

Figure 25 - Save the StockPrice form