Wikicat Technical Design/Resource Relationship Entities

Wikicat (and Wikicite) datamodel for expressing relationships between resources and the primary, FRBR-derived entities of Wikicat. This encompasses both

Resource Realtors

Defines the set of realtors (i.e. relationship types) that may exist between catalog resources.

   > DESC resource_realtor;
    
    COLUMN        TYPE          DESC
    ----------------------------------------------------
    realtor_code  VARCHAR(5)    Realtor type code/primary key
                  NOT NULL
    name          VARCHAR(25)   Short name of the realtor
                  NOT NULL
                  TRANSLATABLE
    description   VARCHAR(50)   Description of the realtor
                  NOT NULL
                  TRANSLATABLE
    entity1_type  VARCHAR(3)    Type of first entity in the relationship;
                  NOT NULL      foreign key to table MODEL_ENTITY_TYPE
    entity2_type  VARCHAR(3)    Type of second entity in the relationship;
                  NOT NULL      foreign key to table MODEL_ENTITY_TYPE
    commutative   BOOLEAN       If the relationship is commuative
                  NOT NULL
    transitive    BOOLEAN       If the relationship is transitive
    transactional BOOLEAN       If the relationship is transactional, for example
                                with a fixed duration

  > select * from resource_realtor;
    
    realtor_code  name        entity_type1 entity_type1  commutative
    ----------------------------------------------------------------
    exprs         Expresses   exp          wrk           FALSE
    mnfst         Manifests   mnf          exp           FALSE
    embdy         Embodies    itm          mnf           FALSE

Resource Relationships

   > DESC resource_relationship;
    
    COLUMN        TYPE          DESC
    ----------------------------------------------------
    res_rel_id    INT(15)       Primary key of the entity
                  NOT NULL
    realtor_code  VARCHAR(5)    Relationship type; foreign key
                  NOT NULL      to RESOURCE_REALTOR
    entity1_type  VARCHAR(3)    Type of the first entity in the relationship;
                                foreign key to table MODEL_ENTITY_TYPE
    entity1_id     INT(15)       Primary key of the first relationship entity;
                  NOT NULL      the actual table to which this
                                key points is indicated by entity_type.
    entity2_type  VARCHAR(3)    Type of the second entity in the relationship;

The extent of a resource relationship, such as pages, playing time, etc. Note that there may be mutliple extents for a particular resource relationship when the extents are non-consecutive.

 > DESC resource_rel_extent;
    
    COLUMN        TYPE          DESC
    ----------------------------------------------------
    res_rel_x_id  INT(15)       Primary key of the entity
    res_rel_id    INT(15)       Resource relationship; foreign
                  NOT NULL      key to RESOURCE_RELATIONSHIP
    extent_type   VARCHAR(3)    Type of the extent
                  NOT NULL
    extent_unit   VARCHAR(3)    Unit of the extent, for example, from
                  NOT NULL      EXPRESSION_EXTENT_UNIT_TYPE 
    extent_begin  VARCHAR(25)   Beginning of the extent
                  NOT NULL
    extent_end    VARCHAR(25)   End of the extent
                  NOT NULL