Table Alternatives

Definition and assumptions

By using table-alternatives annotation, data modelers can define alternative tables (or views) for different contexts. By definition, the table that you are adding this annotation to will be the base table and all the listed tables are alternative tables for this base table (Link to the initial issue).

The following are constraints for alternative tables:

1. Alternative tables cannot have any inbound foreignkeys.

2. A base table cannot be an alternative table for other base tables (i.e. flat 2-level forest).

3. Alternative table has exactly one base table.

4. Alternative table must have exactly one not-null unique key that is a foreign key to the base table.

5. All alternative tables associated with the base table have not-null unique keys that are foreign keys to the SAME primary keys of the base table.

Related Entities

• Alternative tables should not be considered as related entities of the base table.

• Since alternative table cannot have any inbound foreignkeys, the related entities for an alternative table always returns an empty list (In the future we can change this behavior to return the related entities of the base table).

Understanding Recordset

To understand the impact of different annotations and heuristics, it is helpful to imagine that the recordset application proceeds with a sequence of decisions:

1. The application is started on a given tablename encoded in the URL.

2. If the tablename in the URL has an alternative table declared for the compact context, the application presents results from this alternative table instead of the base table named in the URL.

3. The visible columns annotation for the filter context of the results table is consulted to populate the filtering sidebar, and heuristics are applied to the results table if no matching annotation is found.

   • So, if a compact alternative table is being presented, facets are chosen for this alternative table.

4. All facet definitions MUST be expressed in terms of the results table from which they are derived. I.e. the source of the facet MUST be a column of the result table or a path beginning with a foreign key connected to the result table.

   • Since alternative tables have only one foreign key, a facet path from an alternative table MUST first traverse the foreign key connecting it to the base table, then continue on any further path through the model as desired.

5. Each facet which is in entity-selection mode will consider the compact/select context presentation instructions of the facet table (e.g. the rightmost table in the facet’s source path). This MAY introduce another alternative table used to present the choices visible in the facet widget and the entity selection modal.

ERMrestJS Restrictions

The filter context is not supported under the alternative-table annotation.

Faceting

1. Alternative Table For Main Table

The following sections are based on this Entity Relationship diagram, and also the table-alternatives annotation on base table:

"tag:isrd.isi.edu,2016:table-alternatives": {
    "compact": [ "schema", "compact alt"],
    "detailed": [ "schema", "alternative alt"]
}

Therefore we have to show compact alt in compact, and detailed alt in detailed context.

Scenario 1 - Faceting Annotation

In chaise, we are using compact context for recordset app, which has the faceting features. If an alternative table is defined for the compact context, when users navigate to the base table in the URL, the facet list will be derived from the list defined on the alternative table. Therefore you MUST define the facet list on the alternative table. The facet list on the base table will be ignored.

The following is an example of how we can define facet annotation in alternative tables. As you can see the source path starts from compact alt.

"annotations": {
    "tag:isrd.isi.edu,2016:visible-columns": {
        "filter": {
            "and": [
                {
                    "source": [{"outbound": ["schema", "fk1"]},  "col"],
                    "markdown_name": "Column in Main Table"
                },
                {
                    "source": [{"outbound": ["schema", "fk1"]},  {"inbound": ["schema", "fk3"]}, "baseID"],
                    "markdown_name": "Related Entity"
                },
                {
                    "source": "compact col",
                    "markdown_name": "Column in Alternative Table"
                }
            ]
        }
    }
}

This will result in showing three facets: col (from base table), baseID (from related_table table) , compact col (from comp alt table).

Scenario 2 - Heuristics (Without Faceting annotation)

If the annotation was not defined, ermrsetjs will apply the following heuristics for generating the facet list:

1. Create facets for visible columns in compact context. Columns participating in outbound fks will be presented as an entity facet (instead of scalar facet).

2. Create facets for related entities in detailed context. For each of the related tables R,

   2.1. If there isn’t an alternative table defined for detailed context: Create an entity facet based on R.

   2.2. If there is an alternative table defined for detailed context: Since alternative tables don’t have any related entities, no extra facet control will be added (until we change the heuristics to automatically derived proper related tables based on the base table mentioned above).

Example: scenario 2.1—Heuristics, without detailed alternative table

"tag:isrd.isi.edu,2016:table-alternatives": {
    "compact": [ "schema", "compact alt"]
}

In our example for 2.1, with no facet annotation, you will see two facets: compact col (from comp alt table), baseID (from related_table table).

Example: scenario 2.2—Heuristics, with detailed alternative table

"tag:isrd.isi.edu,2016:table-alternatives": {
    "compact": [ "schema", "compact alt"],
    "detailed": [ "schema", "alternative alt"]
}

Based on the explanation of previous scenario, since alternative tables don’t have any related entities, no extra facet control will be added (until we change the heuristics to automatically derived proper related tables based on the base table mentioned above). So you will only see compact col (comp alt table) in the list of facets.

2. Alternative Table in Facet List

In entity mode, what users will see is some rows from the table that you have defined a path to in the annotation. The context that is being used to generate these rows is compact/select. Therefore, if you have defined an alternative table for one of the facet tables in this context, we are going to show the alternative table.

If the wrong tables are used in the facet list, this might cause a problem. To better understand this issue, let’s explain based on different scenarios.

This is just for entity mode. In scalar mode we’re not changing the table and alternative table logic is not going to be applied.

Scenario 1: Entity facet with alternative table for compact/select

In the annotation, you can either use the facet base table or compact alt in your path. Regardless, ermrestjs will apply the facet on compact/select table of the facet base table. In order for Ermrest to generate the facet correctly, the facet of the base table has to be defined on the key column that is being used by the alternative table. If the facet is defined on a different key column, Ermrestjs will treat this as an ERROR case and will ignore this facet.

Example: Proper facet source path

Facet List for table main:

"filter": {
    "and": [
        {"source": [{"inbound": ["schema", "FK1"]}, "ID_base"]}
    ]
}

Since we’re going to show the table in compact/select, you will see compact alt in your list of facets.

Example: Invalid facet source path

In the following scenario, ERMrestjs cannot change the table and will just ignore this entry from facet list.

"filter": {
    "and": [
        {"source": [{"inbound": ["schema", "FK1"]}, "mainanother ID"]}
    ]
}

Scenario 2: Table With Alternative For compact/select, Facet on Another Alternative Table (Not supported)

Entity Relationship Diagram:

"filter": {
    "and": [
        {"source": [{"inbound": ["schema", "FK1"]}, "ID"]}
    ]
}

In this scenario, we are faceting based on detailed alt table. ERMrestjs is not supporting this case, and if encounters this scenario will ignore this entry from facet list.

Scenario 3: Table Without Alternative For compact/select, Facet on Another Alternative Table (Not supported)

Entity Relationship Diagram:

"filter": {
    "and": [
        {"source": [{"inbound": ["schema", "FK1"]}, "ID"]}
    ]
}

In this scenario, we are faceting based on detailed alt table. ERMrestjs is not supporting this case, and if encounters this scenario will ignore this entry from facet list.

All not-supported cases and invalid cases will be ignored!