- Introduction to WorkXpress
- Building Your Application
- Examples and Best Practices
- Technical Manual
This shows you the differences between two versions of the page.
|
relationship table [2016/09/14 18:19] |
relationship table [2016/09/14 18:19] (current) |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | ====== Relationship Table ====== | ||
| + | |||
| + | Relationships are the essential Building Block that enable complex linkages across diverse sets of data. Relationships are essential to enabling complex, interwoven content. | ||
| + | |||
| + | * **Relationships** refers to the conceptual Building Block, one of the 5 fundamental building blocks | ||
| + | * **Relationship Table** refers to a set of relationships defined by the builder. An example is the "employee-employer" relationship table that links some "Employees" to some "Companies" | ||
| + | * **Relationship** refers to a specific instance of a relationship table, usually created by a user. An example, Tom Smith is an "employee" of Acme Company, and Acme Company is the "employer" of Tom Smith. | ||
| + | |||
| + | ===== Creation ===== | ||
| + | |||
| + | "Creating a Relationship Table" refers to the act of creating the database entries to allow for two or more tables to be related. Relationships themselves are typically created by the user during the normal course of using the application. | ||
| + | |||
| + | ==== How To Create ==== | ||
| + | |||
| + | Relationships are created in context during the normal course of building your application. For example, while creating in the [[Query Builder]], or while adding a [[Field Type - Selection|selection field]] to a [[Form Type - Field Grid|field grid]]. Specifically, there are four contextual methods for creating relationship tables: | ||
| + | |||
| + | Method 1: Using the [[Query Builder]] | ||
| + | Method 2: From within the [[Form Type - List|list]] wizard | ||
| + | Method 3: From within the [[Field Type - Selection|selection]] wizard | ||
| + | Method 4: From within the Query Builder tool, specifically, when using the "Add Table" Wizard | ||
| + | |||
| + | Methods 1 through 3 above will launch the "Add Relationship" wizard. Method 4 above will create a relationship table automatically. | ||
| + | |||
| + | ==== Wizard Questions ==== | ||
| + | |||
| + | The "Add Relationship Table" wizard needs to know 4 things. However, it often already knows at least 2 of the answers, and doesn't prompt for these. The questions are: | ||
| + | |||
| + | - What table is on the "left" side of the relationship | ||
| + | - What table is on the "right" side of the relationship | ||
| + | - What is the label for the "left" side of the relationship | ||
| + | - What is the label for the "right" side of the relationship | ||
| + | |||
| + | As an example, lets assume that we want to create a new Relationship Type that connects Companies to Employees as employer-to-employee. Our answer to question 1 is "Company", 2 is "Employee", 3 is "employer", and 4 is "employee". | ||
| + | |||
| + | In many cases, however, there is already significant context for the relationship table, and as a result, the answers to questions 1 and 2 may already be assumed. For example, if you are adding a [[Form Type - List|list]] to a page about a Company, the wizard knows that at least one side of your relationship is probably Company, and answers question 1 for you. It may present a very simple select box to let you choose the answer to question 2. | ||
| + | |||
| + | In the Query Builder the answers to questions 1 and 2 are both known, and the wizard asks you for the answers to questions 3 and 4. It always assumes that these answers are simply the name of the tables and pre-populates those responses. | ||
| + | |||
| + | In a rare case (How to Create: Method 4 above) the wizard assumes all four answers. | ||
| + | |||
| + | NOTE: The terms "left" side and "right" side are meaningless in WorkXpress, they are just a means of establishing an order for answering the four questions, and were required for choosing a direction to display the Relationship out of context. Within the application, however, the relationship will always have context. This means the relationship could display as "employer to employee" on a Company page, but as "employee to employer" on a Employee page. The original sides lose all importance when in context. | ||
| + | |||
| + | ==== Automations ==== | ||
| + | Creating a new relationship table typically does not involve any automation in and of itself. Depending on the context, other Blocks may create automations around the relationship. For example, when creating a [[Field Type - Selection|selection field]], the field itself will automate the creation of some [[Action|actions]] to properly manage the creation and deletion of relationships of this type. | ||
| + | |||
| + | === Automation Example === | ||
| + | Let's say you are a payroll company that deals with many companies. | ||
| + | |||
| + | You have a Company table and an Employee table. | ||
| + | On the Company table, you can have a multi-select field (Employees) to select all the employees that work for that company. | ||
| + | On the Employee table, you have a single select field to select the company the Employee works for (Company). | ||
| + | |||
| + | Both of these fields manage the same relationship, Employee to Company. | ||
| + | |||
| + | However let's say you also want to know which employee is the owner. To do this, you create a single field on the Company table called Owner and have this manage the same relationship (Employee to Company) as the other field (Employees). | ||
| + | |||
| + | Or, you can create a new relationship called Company to Owner that will be managed by the Owner field on the Company table. So you have two relationships between the same two tables, but the relationships are handling different information. | ||
| + | |||
| + | ==== Usage ==== | ||
| + | Relationships are used primarily within the Query Builder. They are most commonly a tool to define connections between tables, thereby allowing the identification of exactly the right records for a given purpose. | ||
| + | |||
| + | ==== Editing ==== | ||
| + | To edit the settings of a relationship table, you must use the WorkXpress [[Handbook]]. | ||
| + | |||
| + | ==== One-to-One, One-to-Many, Many-to-Many ==== | ||
| + | WorkXpress relationships are able to function in a one-to-one, one-to-many, or many-to-many role without additional configuration. It all depends on usage. You can use [[Field Type - Selection|selection]] [[Field|field]] [[Field Setting - Define Field Search|relationship management]] to force a relationship to function as one-to-one or one-to-many. A single selection field managing a relationship will force the relationship to function as one-to-many. A single selection field on each side of the relationship would force the relationship to function as one-to-one. A multi-selection field managing the relationship will aid in relationship creation, but will not change the usage of the relationship. | ||
| + | |||
| + | ==== Storing Data on Relationships ("Many-to-Many") ==== | ||
| + | WorkXpress relationships also have a unique usage. Like tables, they are a place with which a field can be associated, and as a result, on which data can be stored. Storing data on relationships from a technical standpoint is the equivalent of creating a "many-to-many" data table in a relational database. However, it does not require the intermediary tables commonly associated with that type of relationship. Using a relationship as a data storage location, rather then creating an intermediary table, greatly simplifies the architecture of many common functions, such as attaching Products to an Invoice via a Line Item. In WorkXpress, you simply store fields like "Quantity" and "Extended Price" on the relationship itself! | ||
| + | |||
| + | === How to Store Data on a Relationship === | ||
| + | There are two methods to create a field that is associated with, or "about", a relationship. | ||
| + | |||
| + | * Using the [[Block Creator]], click to create a [[Field|field]]. For the table to attach the field to, choose the relationship you want the data to be stored on. | ||
| + | • Using the [[Block Editor]], edit an existing [[Field|field]], go to the [[Field Setting - Global Block Association|block association]] tab, and attach the field to the desired relationship. | ||
| + | |||
| + | ==== Referential Integrity and Recycling/Deleting a Relationship ==== | ||
| + | Referential integrity is required when you wish to enforce the existence of both sides of a relationship at all times. Traditional concepts of referential integrity can easily be enforced with simple actions. In WorkXpress referential integrity represents more of a validation process using actions than a strict data process. | ||
| + | |||
| + | However, WorkXpress does not allow a relationship to exist with only one side. Recycling or deleting an item necessitates the recycling or deletion of any relationships to it: | ||
| + | |||
| + | * When a [[Record|record]] is recycled, its relationships are also recycled. Restoring that record also restores its relationships. | ||
| + | * When an record is deleted, its relationships are also deleted. This deletion is permanent and cannot be undone. | ||
