REFERENCE LIBRARY
LEARNING CENTER

Working with Databases

To continue with this content, please log in with your Data Access ID or create a new account.
Cancel Data Access ID
You may not be authorized to see this content. Please contact Data Access Europe for more information.
Cancel Data Access Europe
You are not authorized to see this content.
Cancel Data Access Europe

DataFlex courses

Information

Working with Databases
Introduction (0m 38s)
Define related tables (7m 05s)
Next lesson:
Access data using global buffer
Cancel

Working with database

Lesson 4: Define related tables

Since it is a relational database additional tables are needed to relate to.

  1. From the ‘Table Explorer’ on the left, select the ‘Create New Table’ icon from its top menu.
  2. From the ‘Create New Table’ window that opens, select the desired ‘Connection id,’ which is ‘cookbook’ for this table.
  3. Add a ‘Table Name.’ “Kitchen” will be the name of this table. Select ‘OK.’
  4. Add field/column names:
  5. FIELD 1 - Name: KitchenID, Type: int, Size: 10, DataFlex Type: Numeric
  6. This KitchenID will be the unique identifier.
  7. From the ‘Column Properties’ panel on the right, set the ‘is identity’ to “True” to allow SQL to provide the identities. This makes it so every new record will automatically get a unique KitchenID.
  8. FIELD 2 - Name: Name, Type: varchar, Size: 50, DataFlex Type: ASCII
  9. Add a unique index for the table. From the ‘Indexes’ panel (mid-screen):
  10. Select ‘Add Index’ from the top of the panel.
  11. ‘Index 1’ is automatically added to the ‘Indexes’ list, and ‘KitchenID” is added to the column list making it the unique index.
  12. Optional: Select ‘Index 1,’ and from the ‘Properties’ panel on the right change the ‘sql primary key’ to “True” to make it work faster with SQL.
  13. Again, select ‘Add Index’ from the top of the panel creating ‘Index 2’, and select ‘Name’ for its column.
  14. Save the table.
  15. View the table by right clicking on ‘Kitchen’ from the ‘Table Explorer’ on the left, and selecting ‘View Table.’ The table will be empty.

  16. For example purposes, some test records are added.
  17. Define the relationship between the ‘Kitchen’ and ‘Recipe’ tables…
  18. Right click on the ‘Recipe’ table from the ‘Table Explorer’ on the left and select ‘Edit Table.’
  19. Select the ‘Relationships’ tab in the middle of the screen.
  20. Select ‘Add Relationship’ from the top of the panel and select the ‘Kitchen’ table from the ‘Add a Relationship’ window that appears.

  21. Under ‘From Recipe’ choose the top empty field and select ‘KitchenID’ from the exposed drop down.
  22. Under ‘To Kitchen’ choose the top empty field and select ‘KitchenID’ from the exposed drop down.
  23. Select ‘Add’ from the top of the panel to save the relationship.
  24. Expand the ‘Recipe’ section of the ‘Table Explorer,’ and then expanding ‘Relationships’ and ‘Kitchen’ will expose the relationship just created.
  25. From the ‘Table Explorer’ on the left, select the ‘Create New Table’ icon from its top menu.
  26. From the ‘Create New Table’ window that opens, select the desired ‘Connection id,’ which is ‘cookbook’ for this table.
  27. Add a ‘Table Name.’ “Ingredients” will be the name of this table. Select ‘OK.’
  28. Add field/column names:
  29. FIELD 1 - Name: IngredientID, Type: int, Size: 10, DataFlex Type: Numeric
  30. This IngredientID will be the unique identifier.
  31. From the ‘Column Properties’ panel on the right, set the ‘is identity’ to “True” to allow SQL to provide the identities. This makes it so every new record will automatically get a unique IngredientID.
  32. FIELD 2 - Name: RecipeID, Type: int, Size: 10, DataFlex Type: Numeric
  33. This will be the foreign key. There is a multiple to one relationship between ‘RecipeID’ and ‘IngredientID’ because multiple ingredients can be applied to a since recipe.
  34. FIELD 3 - Name: Name, Type: varchar, Size: 50, DataFlex Type: ASCII
  35. FIELD 4 - Name: Amount, Type: varchar, Size: 50, DataFlex Type: ASCII
  36. Add a unique index for the table. From the ‘Indexes’ panel (mid-screen):
  37. Select ‘Add Index’ from the top of the panel.
  38. ‘Index 1’ is automatically added to the ‘Indexes’ list, and ‘IngredientID’ is added to the column list making it the unique index.
  39. Optional: Select ‘Index 1,’ and from the ‘Properties’ panel on the right change the ‘sql primary key’ to “True” to make it work faster with SQL.
  40. Again, select ‘Add Index’ from the top of the panel creating ‘Index 2’, and select ‘Name’ for its column. 
  41. Add a second column to Index 2, ‘IngredientID.’
  42. Again, select ‘Add Index’ from the top of the panel creating ‘Index 3’, and select ‘RecipeID’ for its column. 
  43. Add a second column to Index 3, ‘Name.
  44. Define the relationships…
  45. Select the ‘Relationships’ tab.
  46. Select ‘Add Relationship’ from the top of the panel and select the ‘Recipe’ table.

  47. Under ‘From Ingredients’ choose the top empty field and select ‘RecipeID’ from the exposed drop down.
  48. Under ‘To Recipe’ choose the top empty field and select ‘ReceipeID’ from the exposed drop down.
  49. Select ‘Add’ from the top of the panel to save the relationship.
  50. View the table by right clicking on ‘Ingredients’ from the ‘Table Explorer’ on the left and selecting ‘View Table.’ The table will be empty.
  51. For example purposes, some test records are added.
  52. Save the table.
©2024 Data Access Worldwide, all rights reserved
Privacy policy Cookies About dataaccess.com Online Documentation