When to Use Non-Unique Fields for Detail Tables?
Required Permissions
This section requires the Application Designer and Storage Management permissions from your Domain's administrator.
Check What are the Design Tasks? to learn more about permissions to work with Design Mode and other tools.
Abstract
Introduction¶
The How to Create a Detail and Attach It to a Row section specifies that you can create a Detail table without selecting neither unique fields nor a Primary Key. This document helps you understanding if you need a Detail table based on non-Unique fields.
Sections Recommended Configuration: Details Built on Unique Fields and Primary Key and Details Built on Non-Unique Fields explains the features of Details with and without Unique fields and Primary Keys.
The flowchart Discover the Detail Table You Need helps taking a decision.
Recommended Configuration: Details Built on Unique Fields and Primary Key¶
The recommended configuration to create a Detail table is the following:
- a Unique field (e.g. an Identity-type field) as Source Field in a Storage's Relation.
- a Numeric-type field as Target Field in a Storage's Relation.
- a Primary Key to link Detail table's rows and Master rows.
Info
See Create an Empty Storage to Be Filled for more information about Relations, source fields, and target fields.
If you use Unique fields as Source Field in the Relation, the application links the Detail rows only to a single Master row. Each Detail row records a value in the Target Field that is unique and connected only to that Master Row. The Primary Key relates the Detail rows only to that particular Master Row.
Details Built on Non-Unique Fields¶
In certain circumstances, it's useful instead to create a Detail table without a Primary Key by using non-Unique fields.
Having a Detail table built on a Relation between non-Unique field means that Master rows will share one or more Detail rows when they have the same value for the Source Field.
If two Master rows have the same value recorded in the Source Field, the application displays the same Detail rows with the same value in the Target Field in both Master rows' Details.
Example
An application records a transport company's bus routes. The application has a Detail recording the buses employed from the garage and their technical info. The String field "NumberPlate" records the bus operating on the route through the number plate. Different routes' Master rows have in common the same buses' Detail rows through the "NumberPlate".
Note
Design Mode supports only the following Relations between non-Unique fields to create Details without selecting a Primary Key:
- Source Field: Numeric Target Field: Numeric
- Source Field: String Target Field: String
Other combinations (e.g. String Date) are not supported.
Discover the Detail Table You Need¶
Follow the flowchart to understand what you should do as Designer.
%%{init: {"flowchart": {"htmlLabels": false}} }%%
flowchart TD
begin(("`You must create an application containing **Detail** tables`"))-->question1{"`Must _Master_ table's rows share **Detail** rows?`"}
question1-->|No|answerUnique1["`Use a **Unique** field as **Source Field** in the **Relation**. Select a **Primary Key** in _Design Mode_`"]
question1-->|Yes|question2{"`Do you need a **Detail** table with separated rows
_and another_ **Detail** table with rows shared by two or more _Master_ rows?`"}
question2-->|Yes|answerUnique2["`Create the first Detail by using a **Unique** field as **Source Field** in the **Relation**. Select a **Primary Key** in _Design Mode_`"]
answerUnique2-->end1["`Create the Detail that will have shared rows across _Master_'s rows by using **non-Unique** fields in the **Relation**. _You don't need_ to configure a **Primary Key** in _Design Mode_`"]
question2-->|No|answerUnique3["`Create the Detail that will have shared rows across _Master_'s rows by using **non-Unique** fields in the **Relation**. _You don't need_ to configure a **Primary Key** in _Design Mode_`"]