Class Summary
Class	Description
AbstractReference	Use a reference for the following reasons: Light graph relations that support multiple `AbstractReference.getRelated()` relations Entities that do not want to carry contextual relation with them.
ActivityRef	`Reference` to an event or activity that has a physical or meta-physical presence.
AffiliateRef	This represents `Reference` to an Affiliate org, or person
DataRef	`Reference` to Some data.
DesignationRef	This represents add the `DesignationRef`s `PersonRef`s may have
OrganizationRef	This represents a `Reference` to an Organization
PersonOrgRef	This represents references that can be a `PersonRef` or an `OrganizationRef`.
PersonRef	This represents all people that we maintain ref-integrity with other entities. A `Reference` to a `PersonRef` can have `Reference.getParent()` as `OrganizationRef` and `Reference.getRelated()` as `DesignationRef`
PlaceRef	`Reference` to a place , venue etc.
PointRef	Represents the `Reference` of a `PlaceRef` with `GeoLocation` of any Place. WARN : A `PointRef` should not have the same reference name as any order place
Reference
ReferenceAlias	Allow a Reference to map other References as its alias
ReferenceAliasKey
ThingRef	`Reference` to Some thing.

Package com.neurosys.refs.domain Description

Introduction

See some entities:

Some general rules have been kept in mind (guidelines), while designing the above type entities and their relations:

Reference types are usually FetchType.LAZY ; because they represent connection graph. However in OneToOne relations you will want to keep it EAGER since its anyway part of the same table ususally.
Reference types are used where we wish to keep a weak/light dependencies. They load the entire relation graph
Reference types deletion does not impact any Master table that it represents. Thus if a master recrd has to be deleted it can, while leaving the reference intact. This allows all data (current and old) to maintain the reference point even if the record does not need to exist.
Reference types that also support GUID become cross DB available without need to carry other additional master data overhead with them.
Only direct relation like Entity with its EntityAttributes or Entity and its parent etc are FetchType.EAGER and maintain a Strong Ref-Integrity.
Any OneToMany or Many type relation with References, should consider using CollectionTable, as it creates a transient/transaction/link table between the entity and its relations. However DDL can get complex, constraints in these should be checked.

Entity level considerations

Reference Graph

Similar to Products, Reference maintains a single Table design for hierarchy in reference table. However unlike product, it has the following differences:

It does not contain any additional information other than pure relational context/connections to its parent Reference.getParent().
An example of Reference parent-child relation is a 'director' is of Type PersonRef which is of type Reference.
Another example is 'manufacturer' is of type com.neurosys.refs.domain.Organization is of type Reference.
Producer an be an com.neurosys.refs.domain.Organization or a PersonRef or it directly inherits from Reference
A graph is created to to support Alias/side relations since a Parent-Child is strict in the sense that a child can only one parent.
A graph is created using ReferenceAlias. This allows relations like A PersonRef can be a 'director' or a 'cast' member. Note: director and cast both are PersonRef type

So visually our graph looks something like this:

                        Reference type
                        |
                        \__ Organization type
                        |     |__ manufacturer record
                        |
                        \__ Person type
                              |
                              \__ director record. Note: director here can be a type or a record. It does not matter, however in terms of software code reusability and flexibility a record is more useful
                              |     ^
                              |     |
                              \__ Director abc record with a com.neurosys.refs.domain.ReferenceAlias#setRelated(com.neurosys.refs.domain.Reference) set to 'director'. Note: related because the same person can also be a Producer or an Actor etc.
                              \__ Director xyz record with a com.neurosys.refs.domain.ReferenceAlias#setRelated(com.neurosys.refs.domain.Reference) set to 'director'

Relations

1-to-1 Relation between `com.someproject.domain.catalog.ProductArtifact` and `Reference`

Example: com.someproject.domain.catalog.ThreeDObject#getManufacturer() Tech Notes: FetchType.EAGER loading.
This uses a OneToOne relation.

1-to-many Relation between `com.someproject.domain.catalog.ProductArtifact` and `Reference`

Example: com.someproject.domain.catalog.SongBooklet#getDirectors() All relations between product and reference go into a single Table product_refrence. Technically we can have a separate Table for each relation type like product_director (See Dev notes on ORM type) etc; however we want to prevent.
This uses a OneToMany or ManyToMany relation.

Developer Tech Notes

Loading Strategy

FetchType.LAZY Vs FetchType.EAGER loading.
With lazy the entity does not need to pull an entire reference graph with it each time. On getter of the required data the ORM will make the call to fetch the extra data, however many times on get there is no valid EntityManager Transactional available to the thread and the get will fail. Malking the code brittle.

With EAGER loading by default we cannot simultaneously fetch multiple bags Hibernate Exception, so we use it in conjunction with Fetch(FetchMode.SELECT)
See Reference discussion:

                The root cause of the problem is that when Hibernate fetches SQL query results there is no simple way to tell which child element belongs to which collection. See this blog entry for more detailed explanation with an example. To summarize you have following workarounds:
                
                o Load each collection separately using sub-select @Fetch(FetchMode.SELECT)
                o Force usage of list instead of bag by adding index column @IndexColumn(name="LIST_INDEX")
                o Use unordered collection like Set.    
                o Try @LazyCollection(LazyCollectionOption.FALSE) (http://stackoverflow.com/questions/4334970/hibernate-cannot-simultaneously-fetch-multiple-bags)

ORM Types for product_reference

ElementCollection or OneToMany (from Reference type to Product) along with OrderColumn (Optional) and CollectionTable (optional) - Will result in a Weak entity table that is a cartesian product of all columns involved access like the following. This is less desirable because we can see each Product entity has its primary key name mentioned here. In contract, we would prefer a single product reference col product_id:

                        mysql> desc product_reference;
                        +---------------------+--------------+------+-----+---------+-------+
                        | Field               | Type         | Null | Key | Default | Extra |
                        +---------------------+--------------+------+-----+---------+-------+
                        | SongBooklet_id      | bigint(20)   | NO   | MUL | NULL    |       |
                        | singers_name        | varchar(255) | NO   | PRI | NULL    |       |
                        | producers_name      | varchar(255) | NO   | UNI | NULL    |       |
                        | musicDirectors_name | varchar(255) | NO   | UNI | NULL    |       |
                        | lyricists_name      | varchar(255) | NO   | UNI | NULL    |       |
                        | directors_name      | varchar(255) | NO   | UNI | NULL    |       |
                        | casts_name          | varchar(255) | NO   | UNI | NULL    |       |
                        | bookletArtists_name | varchar(255) | NO   | UNI | NULL    |       |
                        | LobbyCard_id        | bigint(20)   | NO   | MUL | NULL    |       |
                        | FilmPoster_id       | bigint(20)   | NO   | MUL | NULL    |       |
                        | imgSubjects_name    | varchar(255) | NO   | UNI | NULL    |       |
                        +---------------------+--------------+------+-----+---------+-------+

... to fix the above we use per entity field for example:

 @Fetch(FetchMode.SELECT)  @ManyToMany(targetEntity=Person.class, fetch=FetchType.EAGER)
         @CollectionTable(
                        joinColumns= @JoinColumn(name = "product_id", referencedColumnName = "id"),
                        uniqueConstraints = { @UniqueConstraint(name="product_singers_name", columnNames = {"product_id","singers_name"})}
        )
        
                        OR
                        
         @Fetch(FetchMode.SELECT)  @ElementCollection(targetClass=Person.class, fetch=FetchType.LAZY)  @JoinColumn(nullable=true)
         @CollectionTable(
                        joinColumns= @JoinColumn(name = "product_id", referencedColumnName = "id"),
                        uniqueConstraints = { @UniqueConstraint(name="product_imgSubjects_name", columnNames = {"product_id","imgSubjects_name"})}
        )       // The issue with   @ElementCollection is that it builds a Unique Key on the foreign Key causes issues.

to get the resultant table def:

                        +---------------------+--------------+------+-----+---------+-------+
                        | Field               | Type         | Null | Key | Default | Extra |
                        +---------------------+--------------+------+-----+---------+-------+
                        | product_id          | bigint(20)   | NO   | MUL | NULL    |       |
                        | singers_name        | varchar(255) | NO   | PRI | NULL    |       |
                        | producers_name      | varchar(255) | NO   | UNI | NULL    |       |
                        | musicDirectors_name | varchar(255) | NO   | UNI | NULL    |       |
                        | lyricists_name      | varchar(255) | NO   | UNI | NULL    |       |
                        | directors_name      | varchar(255) | NO   | UNI | NULL    |       |
                        | casts_name          | varchar(255) | NO   | UNI | NULL    |       |
                        | bookletArtists_name | varchar(255) | NO   | UNI | NULL    |       |
                        | imgSubjects_name    | varchar(255) | NO   | UNI | NULL    |       |
                        +---------------------+--------------+------+-----+---------+-------+
                
                
                More samples
                        
                                         @Fetch(FetchMode.SELECT)  @ManyToMany(targetEntity=PersonRef.class, fetch=FetchType.EAGER)
                         @CollectionTable(
                                        joinColumns= @JoinColumn(name = "product_id", referencedColumnName = "id"),
                                        uniqueConstraints = { @UniqueConstraint(name="product_directors_name", columnNames = {"product_id","directors_name"})}
                        )
                        private List directors;
                                
                         @Fetch(FetchMode.SELECT)  @ManyToMany(targetEntity=PersonRef.class, fetch=FetchType.EAGER)
                         @CollectionTable(
                                        joinColumns= @JoinColumn(name = "product_id", referencedColumnName = "id"),
                                        uniqueConstraints = { @UniqueConstraint(name="product_lyricists_name", columnNames = {"product_id","lyricists_name"})}
                        )
                        private List lyricists;
                                
                         @Fetch(FetchMode.SELECT)  @ManyToMany(targetEntity=OrganizationRef.class, fetch=FetchType.EAGER)
                         @CollectionTable(
                                        joinColumns= @JoinColumn(name = "product_id", referencedColumnName = "id"),
                                        uniqueConstraints = { @UniqueConstraint(name="product_productionHouses_name", columnNames = {"product_id","productionHouses_name"})}
                        )
                        private List productionHouses;

unique Constraints for product_reference

Sample Data with Schema

mysql> select * from reference limit 10;
+-------------+-------------------+------+-------------+
| DTYPE       | name              | meta | parent_name |
+-------------+-------------------+------+-------------+
| person      | 96 Studios        | NULL | NULL        |
| person      | ??                | NULL | NULL        |
| person      | Aarjav Trivedi    | NULL | NULL        |
| person      | Akshay Tanksale   | NULL | NULL        |
| person      | Amjad Khan        | NULL | NULL        |
| person      | Aniket Vishwasrao | NULL | NULL        |
| person      | Anita Raj         | NULL | NULL        |
| designation | artist            | NULL | NULL        |
| person      | Ashwin Anchan     | NULL | NULL        |
| person      | Atul Thakur       | NULL | NULL        |
+-------------+-------------------+------+-------------+
10 rows in set (0.01 sec)

mysql> select * from reference where parent_name is not null limit 10;
+-------------+---------------------------------+------+---------------+
| DTYPE       | name                            | meta | parent_name   |
+-------------+---------------------------------+------+---------------+
| designation | posterArtists                   | NULL | artist        |
| org         | Cinemascope Printers            | NULL | printingPress |
| org         | Movie Print, Bombay             | NULL | printingPress |
| org         | Prasad Process Pvt. Ltd. Madras | NULL | printingPress |
+-------------+---------------------------------+------+---------------+
4 rows in set (0.00 sec)

mysql> select * from reference where name='printingPress' limit 10;
+-------+---------------+------+-------------+
| DTYPE | name          | meta | parent_name |
+-------+---------------+------+-------------+
| org   | printingPress | NULL | NULL        |
+-------+---------------+------+-------------+

mysql> show create table reference;
| reference | CREATE TABLE `reference` (
  `DTYPE` varchar(31) NOT NULL,
  `name` varchar(255) NOT NULL,
  `meta` varchar(255) DEFAULT NULL,
  `parent_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`name`),
  KEY `FKebclmm0u61klx9s6x1gn6g6ac` (`parent_name`),
  CONSTRAINT `FKebclmm0u61klx9s6x1gn6g6ac` FOREIGN KEY (`parent_name`) REFERENCES `reference` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> desc product_reference;
+-----------------------+--------------+------+-----+---------+-------+
| Field                 | Type         | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| product_id            | bigint(20)   | NO   | MUL | NULL    |       |
| storyWriters_name     | varchar(255) | YES  | MUL | NULL    |       |
| scriptWriters_name    | varchar(255) | YES  | MUL | NULL    |       |
| dialogueWriters_name  | varchar(255) | YES  | MUL | NULL    |       |
| imgSubjects_name      | varchar(255) | YES  | MUL | NULL    |       |
| publications_name     | varchar(255) | YES  | MUL | NULL    |       |
| singers_name          | varchar(255) | YES  | MUL | NULL    |       |
| musicDirectors_name   | varchar(255) | YES  | MUL | NULL    |       |
| lyricists_name        | varchar(255) | YES  | MUL | NULL    |       |
| casts_name            | varchar(255) | YES  | MUL | NULL    |       |
| bookletArtists_name   | varchar(255) | YES  | MUL | NULL    |       |
| aliasToTitle_name     | varchar(255) | YES  | MUL | NULL    |       |
| authors_name          | varchar(255) | YES  | MUL | NULL    |       |
| photographers_name    | varchar(255) | YES  | MUL | NULL    |       |
| posterArtists_name    | varchar(255) | YES  | MUL | NULL    |       |
| editors_name          | varchar(255) | YES  | MUL | NULL    |       |
| journalists_name      | varchar(255) | YES  | MUL | NULL    |       |
| nameProducer_name     | varchar(255) | YES  | MUL | NULL    |       |
| artists_name          | varchar(255) | YES  | MUL | NULL    |       |
| directors_name        | varchar(255) | YES  | MUL | NULL    |       |
| productionHouses_name | varchar(255) | YES  | MUL | NULL    |       |
| producers_name        | varchar(255) | YES  | MUL | NULL    |       |
+-----------------------+--------------+------+-----+---------+-------+
22 rows in set (0.01 sec)

mysql> show create table product;
  `product_id` bigint(20) NOT NULL,
...
  `authors_name` varchar(255) DEFAULT NULL,
  `photographers_name` varchar(255) DEFAULT NULL,
  `posterArtists_name` varchar(255) DEFAULT NULL,
  `editors_name` varchar(255) DEFAULT NULL,
  `journalists_name` varchar(255) DEFAULT NULL,
  `nameProducer_name` varchar(255) DEFAULT NULL,
  `artists_name` varchar(255) DEFAULT NULL,
  `directors_name` varchar(255) DEFAULT NULL,
  `productionHouses_name` varchar(255) DEFAULT NULL,
  `producers_name` varchar(255) DEFAULT NULL,
...
  UNIQUE KEY `product_authors_name` (`product_id`,`authors_name`),
  UNIQUE KEY `product_photographers_name` (`product_id`,`photographers_name`),
  UNIQUE KEY `product_posterArtists_name` (`product_id`,`posterArtists_name`),
  UNIQUE KEY `product_editors_name` (`product_id`,`editors_name`),
  UNIQUE KEY `product_journalists_name` (`product_id`,`journalists_name`),
  UNIQUE KEY `product_nameProducer_name` (`product_id`,`nameProducer_name`),
  UNIQUE KEY `product_artists_name` (`product_id`,`artists_name`),
  UNIQUE KEY `product_directors_name` (`product_id`,`directors_name`),
  UNIQUE KEY `product_productionHouses_name` (`product_id`,`productionHouses_name`),
  UNIQUE KEY `product_producers_name` (`product_id`,`producers_name`),
...
  KEY `FK4rxh4epkv29i5sd1vtdk0sw9j` (`authors_name`),
  KEY `FKe30n7gjr1yeb2dfv6o666c9r6` (`photographers_name`),
  KEY `FKtj2h7amvuml3hlah3cx7hmht2` (`posterArtists_name`),
  KEY `FKi99e7d0cqxydw2b6lumy2q4oq` (`editors_name`),
  KEY `FKfhl751sh4ydrgasg805mp4itc` (`journalists_name`),
  KEY `FKmx8r8glxkv83ollbv54immatl` (`nameProducer_name`),
  KEY `FK73dajh9q9ifx7bnxqvvl55ycg` (`artists_name`),
  KEY `FKjo5kh9fd6yy63idw78wjws5j9` (`directors_name`),
  KEY `FKtc2kwgmql9i1y6ag2li08yce7` (`productionHouses_name`),
  KEY `FK8n1005xwkcptnkiqkaffs3uj7` (`producers_name`),
...
  CONSTRAINT `FK4rxh4epkv29i5sd1vtdk0sw9j` FOREIGN KEY (`authors_name`) REFERENCES `reference` (`name`),
  CONSTRAINT `FKe30n7gjr1yeb2dfv6o666c9r6` FOREIGN KEY (`photographers_name`) REFERENCES `reference` (`name`),
  CONSTRAINT `FKtj2h7amvuml3hlah3cx7hmht2` FOREIGN KEY (`posterArtists_name`) REFERENCES `reference` (`name`) ,
  CONSTRAINT `FKfhl751sh4ydrgasg805mp4itc` FOREIGN KEY (`journalists_name`) REFERENCES `reference` (`name`),  
  CONSTRAINT `FK73dajh9q9ifx7bnxqvvl55ycg` FOREIGN KEY (`artists_name`) REFERENCES `reference` (`name`),
  CONSTRAINT `FKjo5kh9fd6yy63idw78wjws5j9` FOREIGN KEY (`directors_name`) REFERENCES `reference` (`name`),
  CONSTRAINT `FKtc2kwgmql9i1y6ag2li08yce7` FOREIGN KEY (`productionHouses_name`) REFERENCES `reference` (`name`),  
  CONSTRAINT `FK8n1005xwkcptnkiqkaffs3uj7` FOREIGN KEY (`producers_name`) REFERENCES `reference` (`name`),
  ...

mysql> select product_id, journalists_name from product_reference where journalists_name is not null limit 5;
+------------+------------------+
| product_id | journalists_name |
+------------+------------------+
|        591 | Atul Thakur      |
|        591 | Avijit Ghosh     |
|        590 | Kevin Lobo       |
|        589 | Rajanish Rane    |
|        587 | Variables        |
+------------+------------------+
5 rows in set (0.00 sec)

mysql> select product_id, producers_name from product_reference where producers_name is not null limit 5;
+------------+---------------------+
| product_id | producers_name      |
+------------+---------------------+
|        600 | Ashwin Anchan       |
|        601 | Ashwin Anchan       |
|        597 | C. Dhandayuthapani  |
|        596 | Deepak Bahry        |
|        599 | Gopat Taiwade Patil |
+------------+---------------------+
5 rows in set (0.00 sec)


mysql> desc product;
+--------------------+---------------+------+-----+---------+----------------+
| Field              | Type          | Null | Key | Default | Extra          |
+--------------------+---------------+------+-----+---------+----------------+
| DTYPE              | varchar(31)   | NO   |     | NULL    |                |
| id                 | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| displayName        | varchar(500)  | YES  |     | NULL    |                |
| hierarchy          | varchar(500)  | YES  |     | NULL    |                |
| parentId           | bigint(20)    | YES  |     | NULL    |                |
| bitmask            | int(11)       | YES  |     | NULL    |                |
| description        | varchar(2048) | YES  |     | NULL    |                |
| modelCode          | varchar(255)  | YES  |     | NULL    |                |
| name               | varchar(255)  | NO   | UNI | NULL    |                |
| productCode        | varchar(255)  | YES  | UNI | NULL    |                |
| relatedGroups      | varchar(255)  | YES  |     | NULL    |                |
| tags               | varchar(500)  | YES  |     | NULL    |                |
| category           | bit(1)        | YES  |     | NULL    |                |
| colour             | bit(1)        | YES  |     | NULL    |                |
| comments           | varchar(1024) | YES  |     | NULL    |                |
 ...
 ...
 -- Note the Reference type OneToOne relations embedded below --
| board_name         | varchar(255)  | YES  | MUL | NULL    |                | - OrganizationRef
| publisher_name     | varchar(255)  | YES  | MUL | NULL    |                | - OrganizationRef
| manufacturer_name  | varchar(255)  | YES  | MUL | NULL    |                | - OrganizationRef
| printingPress_name | varchar(255)  | YES  | MUL | NULL    |                | - OrganizationRef
+--------------------+---------------+------+-----+---------+-------------

mysql> show create table product;
...
  KEY `FK9vfe9m1wyjykb4tjy6arfoud5` (`board_name`),
  KEY `FKrwvntu0udkgmr5ctuk0i8psux` (`publisher_name`),
  KEY `FK33o4k5nmbe8ne2jwmix6iw5hb` (`manufacturer_name`),
  KEY `FKrkpb7cpqnyxfj44mdn43fp8i3` (`printingPress_name`),
...
  CONSTRAINT `FK33o4k5nmbe8ne2jwmix6iw5hb` FOREIGN KEY (`manufacturer_name`) REFERENCES `reference` (`name`),
  CONSTRAINT `FK9vfe9m1wyjykb4tjy6arfoud5` FOREIGN KEY (`board_name`) REFERENCES `reference` (`name`),
  CONSTRAINT `FKrkpb7cpqnyxfj44mdn43fp8i3` FOREIGN KEY (`printingPress_name`) REFERENCES `reference` (`name`),
  CONSTRAINT `FKrwvntu0udkgmr5ctuk0i8psux` FOREIGN KEY (`publisher_name`) REFERENCES `reference` (`name`)
  
mysql> select * from reference;
+-----------------+------------------------+------+--------------------------------+------+------+------+-------------+
| DTYPE           | name                   | meta | GUID                           | alt  | lang | lat  | parent_name |
+-----------------+------------------------+------+--------------------------------+------+------+------+-------------+
| point           | bar-1                  | NULL | bar-1-1KSF                     | NULL | NULL | NULL | NULL        |
| PersonRef       | bar_man                | NULL | bar_man-MDZN                   | NULL | NULL | NULL | NULL        |
| point           | counter_service-1      | NULL | counter_service-1-223344286872 | NULL | NULL | NULL | NULL        |
| OrganizationRef | entertainment-co       | NULL | B1CZMLJ2OTQVO2ECMCBKD0YA8      | NULL | NULL | NULL | NULL        |
| PlaceRef        | Gate-2                 | NULL | Gate-2-I8AO                    | NULL | NULL | NULL | NULL        |
| PlaceRef        | sarvana-bhavan-chennai | NULL | SARVANA-BHAVAN-CHENNAI-002     | NULL | NULL | NULL | NULL        |
| PlaceRef        | sarvana-bhavan-kota    | NULL | SARVANA-BHAVAN-KOTA-003        | NULL | NULL | NULL | NULL        |
| PlaceRef        | socials-HKV            | NULL | socials-HKV-RYCX               | NULL | NULL | NULL | NULL        |
| PlaceRef        | the-bar                | NULL | NULL                           | NULL | NULL | NULL | NULL        |
+-----------------+------------------------+------+--------------------------------+------+------+------+-------------+
9 rows in set (0.02 sec)


Another project example: Venue with multiple places
 @Entity  @Table(name="venue") public class Venue extends DomainObject {
        ...
        
        // The PlaceRefs within a venue with multiple entry points.
         @OneToMany(targetEntity=PlaceRef.class, fetch=FetchType.LAZY) //Any ref relation, should be Lazy. Also omit from equals & hashcode builder
        private List places;

        ...
}

Generates:

mysql> select * from venue_reference;
+----------+------------------------+
| Venue_id | places_name            |
+----------+------------------------+
|        1 | socials-HKV            |
|        2 | sarvana-bhavan-chennai |
+----------+------------------------+
2 rows in set (0.00 sec)

Package com.neurosys.refs.domain