Difference between revisions of "Documentation/AOO4 User Guides/Getting Started/Getting Started with Base/Defining Relationships"
(Created page with "{{DISPLAYTITLE:Defining Relationships}} {{Documentation/GS4BaseTOC |ShowPrevNext=block |PrevPage=Documentation/AOO4_User_Guides/Getting Started/Getting Started with Base/Creat...") |
m |
||
Line 33: | Line 33: | ||
#* Double-click the name of the table. | #* Double-click the name of the table. | ||
#* Or, click the name of the table and then click {{button|Add}} for each table. | #* Or, click the name of the table and then click {{button|Add}} for each table. | ||
− | # Click {{button|Close}} to close the Add Tables dialog when you have added the tables you want. | + | # Click {{button|Close}} to close the Add Tables dialog when you have added the tables you want.< br/> |
Figure 166 shows the Relation Design window in its final state after the relations have been created. At the current state of the process, you will not see the lines connecting the tables. The tables may also be closer together and not display all of their fields. Feel free to drag the tables farther apart and resize them by clicking on their edge and dragging. | Figure 166 shows the Relation Design window in its final state after the relations have been created. At the current state of the process, you will not see the lines connecting the tables. The tables may also be closer together and not display all of their fields. Feel free to drag the tables farther apart and resize them by clicking on their edge and dragging. | ||
Line 39: | Line 39: | ||
You can define the relationship between pairs of tables in two ways: | You can define the relationship between pairs of tables in two ways: | ||
− | * Click and drag the primary key field in one table to the'' ''foreign key field in the other table. When you release the mouse button, a connecting line forms between the two data fields. For example, drag from ExpType in Expense_Categories to ExpenseType in the Expenses table. | + | * Click and drag the primary key field in one table to the'' ''foreign key field in the other table. When you release the mouse button, a connecting line forms between the two data fields. For example, drag from ExpType in Expense_Categories to ExpenseType in the Expenses table. [[Image:AOO41GS8_007.png|500px|center]]<center>''Figure 166: Designation for a 1:n relationships''</center> |
− | [[Image:AOO41GS8_007.png|500px|center]] | + | |
− | <center>''Figure 166: Designation for a 1:n relationships''</center> | + | |
** Or, click the '''New Relation''' icon, highlighted in blue in Figure 166. This opens the Relations window (Figure 167). The available tables are listed in the drop-down lists in the ''Tables involved'' section. | ** Or, click the '''New Relation''' icon, highlighted in blue in Figure 166. This opens the Relations window (Figure 167). The available tables are listed in the drop-down lists in the ''Tables involved'' section. | ||
*** Select a table that holds the primary key from the left-hand drop-down list and the corresponding table holding the foreign key from the right-hand drop-down list. Figure 167 shows the tables Expense_Categories and Expenses selected. | *** Select a table that holds the primary key from the left-hand drop-down list and the corresponding table holding the foreign key from the right-hand drop-down list. Figure 167 shows the tables Expense_Categories and Expenses selected. |
Latest revision as of 14:31, 23 September 2023
Defining Relationships
The structure that we have defined for the database tables assumes some relationships that have not yet been made explicit. The following table shows each database table and its fields.
Expense_Categories | Expenses | People | Sales |
---|---|---|---|
ExpType | ID | PeopleID | SaleID |
AmountSpent | FirstName | PersonID | |
DatePurchased | LastName | Date | |
EmployeeID | Amount | ||
ExpenseType |
Both the Expenses table and the Sales table have fields to identify people: EmployeeID and PersonID. When data is being entered into these tables, the PeopleID values are imported from the People table. This is known as a Foreign Key. The advantage of this relationship is that the FirstName and LastName values associated with the ID appear in only one place, a single row in the People table. If a person's name should change for some reason, only one row in one table needs to be edited. In this very simple example, that is not a very daunting task but in a real situation where there might be tables for salary, medical benefits, vacation, leaves, emergency contacts and much more, updating every entry in all the tables would be prone to error and quickly result in very unreliable data.
A similar relationship exists between the Expenses_Categories and the Expenses table, though the purpose is different. In that case, the values of ExpType serve to control the values that can be entered as an ExpenseType in the Expenses table. This is another form of a Foreign Key.
Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY). |