 | | On Relationships | | | Question | | | Many people ask how one-to-many relationship works, what are keys, reference columns, and the lookups. We’ll try to outline and illustrate it in this document. | | Answer | Let’s assume our application is a simple Contact Management application. This application has the table to store Contacts, designed as simple as a single Name column of Text type. Of course we’ll populate the table with some values:
What is the Id column? Where it comes from? TeamDesk requires every record in the table to be identified with some unique value. In some designs the record could be identified by some code, for example the books record could be identified by its ISBN number, the company - by its stock ticker. In our case the name could not be used for identification and we could simply let TeamDesk assign some incremental numbers to the records – the Id column is created with every table and used as the key by default.
Of course, our goal is to build industrial-strength Contact Management application, and we’ll extend it with Companies table (again, the table design is simple, it’s just a Name column in it, and we’ll let TeamDesk assign the Id).
Now we want our Contact(s) belong to a Company.
What type of the relationship to choose? One person works for many companies – well, sometimes it is true, but it is not our design goal. Many people work for one company – true, indeed!
So the relationship is "one Company – many Contacts". Let’s create one and you will see something like:
One-to-many relationship always refers to key column on "one" side. To know which master record a detail record refers to, the detail table stores the copy of master record's key in a separate column. This column is called "Reference Column". Since we have not created any such columns ourselves, we let TeamDesk create one for us. By default, the name of the column will match the name of the master table (Company). Save the relationship.
Now when editing Contact record you will see something like:
Company column will be rendered as a dropdown, containing the list of companies from "Companies" table. The user is prompted to choose one. Let’s fill in company information for all of our contacts and switch back to list view. What we’ll see?
Ok, the information we have in Contacts clearly identifies the company, but what about displaying company name instead of those cryptic Ids? Here, lookups come to help.
Lookups
Visit the relationship's setup screen and add a lookup column to Name (Company table implied from the relationship). As column names should be unique and we already have Company column we’ll call the lookup Company Name.
Lookup is a sort of "virtual" column. It does not store the value by itself. Being tightly coupled with relationship it uses reference column to identify master record that detail record points to and displays the value from the column specified in lookup’s Value Column property.
As you can see, we do not have Oracle record in our Companies Table yet. Larry Ellison of Oracle has no Company selected in his record, the reference is blank and the lookup evaluates as blank as well.
Name Column
When creating the relationship you may notice Name Column selector on the master table side. The Name Column is the column that best describes the record. TeamDesk chooses the first text column in the table for this purpose, but you can change the selection. When creating the relationship, TeamDesk will also create a lookup column to the name column automatically – in a real life the Name is the first (and the only) text column in "Companies" table, it will be used as the Name Column and TeamDesk will add the lookup called "Company Name". Moreover, when you change the Name Column in the relationship, the lookup’s Value Column property is updated with a new selection (but not the lookup’s name).
Summaries
As you are using "one Company – many Contacts" relationship, from Company "point of view" you have zero to infinite number of related Contacts. You can obtain an aggregate value from this set via summary column.
Summary columns allow you to calculate some function (Total, Average, Minimum and Maximum) on the selected column from the detail table or to calculate the count of selected records. With addition of #Contacts, the summary column calculating the count of related records in Contacts, our Company table would have the following look:
| | Related articles | | | Back to Search Results |
|
