Database Design and Operations

This page contains information and reference about the following topics/questions/how to's

  1. How to manipulate data in an existing data-base table with no primary key
  2. How to achieve optimistic concurrency control of data committed to the data-base
  3. How to define an identity column on your data-base table
  4. Should I use the data-base ID generation mechasims or the ones JPA vendors provide
  5. When to use annotations @Embedded vs. @Embeddable
  6. What inheritance strategies does JPA provide
  7. How to define and implement inheritance between data-base tables
  8. What if my tables will be used heavily for reporting purposes

Database

Database is the heart of JPA, hence a good database is the key to achieve the best performance and also ease of development. JPA can only help you so much if your data-base is not designed well.

See: JPA Performance, Don't Ignore the Database

Best Practices & How To's

Entity Generation from/to data-base tables: see the Entity Section

 Entity Generation from/to data-base tables: see the Entity Section

Tables without a PrimaryKey/ForiegnKey

Tables without a PrimaryKey/ForiegnKey

  • ORM/JPA solutions need to recognize relationship between data-base tables hence PrimaryKey/ForiegnKeys are required to identify unique keys within a data-base table.

No Primary Key

See: No Primary Key

  • it is the the case that tables exist without PrimaryKeys within DW for various reasons
  • Case: can mofidy and add Id column: The best solution in this case is normally to add a generated id to the object and table
  • Case: can NOT modify and add Id column: How would you let JPA know of the required Id so it can identify a unique row within the table for you:
    1. Identify a Business Key
      • a combination of one or more columns (it could be all the columns) that will define a unique row in a data-base table
    2. Treat the business key as an Id @Id or @EmbeddedId
    3. Override the equals(Object obj) and hashCode() according to your PrimaryKey
      • If NOT all of the columns define the business key
      • Why: we do this since for example loading a SET of objects from data-base into a Collection Data-structure would require overriding these 2 methods in java.

Inheritance Strategies

 Inheritance Strategies: 

  • InheritanceType.JOINED
    • Please define a discriminator column even if the JPA provider does NOT require it.
    • See the Entity Section

Relationship(s) between tables

  • See Entity Relationships in the Entity Section

@Version and Concurrency (Optimistic Locking)

  • Read Common Locking Mistakes, Questions and Problems
  • Define a @Version Column in your table
    •  
      • often a column defined as below should do the trick, although the Version column does not have to be a timestam
        • ...
              @Version
              @Temporal(TemporalType.TIMESTAMP)
              @Column(name = "UPDATE_TIMESTAMP", nullable = false)
              @Basic(fetch = FetchType.EAGER)
              Calendar updateTimestamp;
          ...
          
  •  
    •  The JPA provider will auto-populate the Version column for you

      • setting the value manually will result in an exception thrown

    • The following types are supported for version properties: int, Integer, short, Short, long, Long, Timestamp. See java docs for the latest information
    • We are often dealing with an application where multiple users can edit the same entry, hence the use of transactional data-bases, but depending on the programming style & habits if you create transaction locks on the wrong section of your code (As noted in the article above) you might not be able to detect two people modifying the same entry at the same time, hence data-integrity issues might occur, whether it be optimistic or pessimistic locking in use.
    • Often using database locking (database pessimistic locking or database transaction isolation) does not scale to web applications hence the need for optimistic locking to ease the pain.
    • For optimistic locking the solution is relatively simple, the object's version must be sent to the client along with the data (or kept in the http session). When the user submits the data back, the original version must be merged into the object read from the database, to ensure that any changes made are detected.
    • What if my table doesn't have a version column?
    • Do I need a version in each table for inheritance or multiple tables?
      • The short answer is no, only in the root table.
    • Read Handling optimistic lock exceptions
      • OptimisticLockException should RARELY be automatically handled, and you really need to bother the user about the issue. You should report the conflict to the user, and either say "your sorry but an edit conflict occurred and they are going to have to redo their work", or in the best case, refresh the object and present the user with the current data and the data that they submitted and help them merge the two if appropriate.

      @TableGenerator (recommended ID generation)

      • @TableGenerator Usage Example:
        • Annotation Usage/Options
          • @Column(name = "TRANSACTION_ID", nullable = false)
             @Id
             @TableGenerator(name = "CS_TRANSACTION_GEN",
             schema="COST_SHARE"
             ,table = "CS_ID_GEN"
             ,pkColumnName = "SEQUENCE_NAME"
             ,valueColumnName = "SEQUENCE_NEXT_HI_VALUE"
             ,pkColumnValue = "CS_TRANSACTION_GEN"
             ,initialValue = 0
             ,allocationSize = 1)
             @GeneratedValue(strategy = GenerationType.TABLE, generator = "CS_TRANSACTION_GEN")
             Integer transactionId;
            
        • Data-base table
          • content example (see multiple generators in one table
        • note that every vendor has default column names
          • for portability reasons please specify pkColumnName and valueColumnName in your annoration
        • allocationSize: 
          • adjust allocationSize as appropriate
          • for example if you will be inserting lots of rows at once increase this number accordingly
          • This increases performance as JPA pre-allocates ids without going to the data-base for every insert
      • Note
        • You do NOT have to create multiple tables for various generators
        • Good practices are to create a table generator per application or schema depending on your applications requirments

      Versioned Schemas

      • avoid Versioned Schemas if you can
      • as of JPA 2.0 there is no support for dynamically binding tables to object on the fly
        • note: JPA 2.1 (JDK7) might include dynamic entities, but nevertheless Versioned Schemas are not considered a good data-base design practice

      Tables used for reporting

      • For data-base schemas that will be used for reporting purposes heavily, consult with data-warehouse
      • Read Snowflake schema
      • Best practices are to flatten your schema when in data-warehouse environment

      Governance

      • Code Review
      • DBA ER Diagram Review
      • User a version column
      • Define Primary and Foreign Keys
      • Let JPA take care of ID Generation for you (see Entity Secion's @TableGenerator)

      Unit Testing

      Please refer to the unit-testing section

      References

      ​Database Design and Operations