Cross References

A cross reference is a table that contains two foreign keys to other tables. Every Andromeda application has at least one cross reference in it, the table USERSXGROUPS which is built into every application by the builder.

A cross reference links one entity to more than one value of another entity. For example, if an item can be ordered in a choice of colors, you need a cross reference of the item to the colors table to establish the list of allowed values.

As another example, consider users and groups. Each user can be in more than one group, and each group can have more than one user. We need a table that lists values of USER_ID on one side and values of GROUP_ID on the other.

The syntax is pretty straightforward. If we assume there is a table of GROUPS and a table of USERS (which actually there is in Andromeda), then the cross-reference looks like this:

table usersxgroups:
    module: security
    description: User-Group Cross Reference
    
    foreign_key users:
        primary_key: “Y”
        uisearch: “Y”
    foreign_key groups:
        primary_key: “Y”
        uisearch: “Y”

The table will end up with two columns. One column is USER_ID, the primary key of the USERS table. The other column is GROUP_ID, the primary key of the GROUPS table.

Also we see that the foreign keys themselves are declared to be the primary key of this table. The primary key of this table comes from the two foreign keys, it is USER_ID + GROUP_ID.