Table references are relationships between tables. Types of relationships include Parent-to-Parent, Parent/Child, Child-to-Parent and Lookup. At a higher, more strategic level, table references are identified according to the size of the subset of records within the relationship. These identifications include One-to-One, One-to-Many and Many-to-Many.
Examples of relationships between tables include the relationship of a:
- Customer Invoice record to the individual line items within that invoice
- Vendor Invoice to be paid to the details of the vendor who sent the invoice
- Item carried in inventory to the details of the storage location(s) where that item is available for picking
- General Ledger account in the Chart of Accounts to the transactions that define the current balance in the account
A number of different types of table relationships are represented in the image below and described in the text beneath the image.
BLUE BOX – The Blue box represents a family or data stack. This family takes the name of it’s highest-level table, in this case the ‘New Car Inventory’ table is the Parent Data Table of this family. Within this family there are Child Tables (Trucks, Cars, SUV) and grandchild tables (Car Type). Child and Grandchild tables are Compositions the Parent table and are explained in greater detail in the Parent-Child Table section of this user’s manual.
A – The links (table relationships) marked “A” in the image above represent Parent-to-Parent (or Master-to-Master) table relationships. Each of these relationships is represented by the line/arrow that starts with the table requesting data content and concludes at (arrow points to) the table providing data content. In the case of all of the links marked “A”, the New Car Inventory parent table is requesting information from the Colors, Upgrades tables regarding the specific configuration of each vehicle in the new car inventory.
B – The links marked “B” in the image above represent Child-to-Parent links. This type of relationship (link) is different than the Parent/Child link in that the P/C link refers to the relationship between a child in a family and the parent of that family (The child’s parent), while a Child-to-Parent link refers to the relationship between a child of one family and the parent of another family. So while the P/C link refers to a relationship within the family the Parent-to-Child refers to links across multiple families.
C – The links marked “C” in the image above represent Child-to-Child links. This type of relationship (link) refers to a relationship between the children of two different families.
NOTE – It is important to note that when any member of any data stack – Family – has a relationship with any member of another family, all members of both families are now related. In the example displayed in the image above, while there is not direct link between, for example, the Cars table and the Sales Team table, there is indeed a relationship between the two in that cars are purchased by customers, and customers buy those cars from members of the sales team. Using these extended family relationships in formulae within Ninox is detailed in the Extended Family References section of this user’s manual.
These, and other types of table relationships are described in greater detail in the Ninox Solutions section of this user’s manual.