title: Hibernate mapping: one column to multiple tables
body: <p>I have a 'best practice' question for a scenario.</p>
  
  <p>Scenario:
  Multiple entities in a DB, for example, Document, BlogPost, Wiki can be shared by individuals. Instead of creating a share table for each entity, a single Share table is created. The issue is, how to map the share table with different entities?</p>
  
  <p>I have three options, please advise which option is best, and if there is a better option.</p>
  
  <p><strong>Option1:</strong>
  Create table Shares as:</p>
  
  <pre><code>SHARES  
  id (unique)
  entityId (non DB enforced FK to DOCUMENTS, WIKIS, POSTS etc.)
  entityType
  sharedBy
  sharedWith
  sharedDate
  </code></pre>
  
  <p>Here, entityId will be a FK to documentId, wikiId, postId etc. etc. and entityType will identity what type the entityId is.</p>
  
  <p>This has issues in Hibernate modelling, when creating Share to entity mapping, such as share.getDocument() or share.getWiki() etc. </p>
  
  <p><strong>Option 2:</strong>
  Create table Shares which only holds share information, and then create resolution tables that tie the share to the entity.</p>
  
  <pre><code>SHARES
  id(PK)
  sharedBy
  sharedWith
  sharedDate
  shareType (helper field for searches)
  
  SHARES_DOCUMENTS
  share_id (unique ID and FK, one to one with SHARES)
  document_id (FK to DOCUMENTS)
  
  SHARES_POST
  share_id (unique ID and FK, one to one with SHARES)
  post_id (FK to POSTS)
  
  more share tables here.
  </code></pre>
  
  <p>So, hibernate wise, Share can have one to one for each of the share types (like share.getDocument(), share.getPost(), and shareType will identify which relationship is 'active' )</p>
  
  <p><strong>Option 3</strong>
  Similar to option 1, but create individual columns instead of entity id</p>
  
  <pre><code>SHARES
  id (unique ID)
  documentId (FK to DOCUMENTS, nullable)
  postId (FK to POSTS, nullable)
  wikiId (FK to WIKIS, nullable)
  sharedBy
  sharedWith
  sharedDate
  sharedType
  </code></pre>
  
  <p>Here, each column could be mapped to respective entity, but they are nullable. sharedType can identify which relationship is 'active'.</p>
  
  <p>So, the question is , which practice is best, both database wise as well as hibernate mapping (and eventual querying, performance wise).</p>
  
  <p>Thanks
  M. Rather</p>