OpenMDM Database Conventions

Owned by Robbie Gehbauer (Unlicensed)
Last updated: Dec 08, 2016

The team has met and decided to change some things about the schema as part of incorporating flyway.  Since we were writing a V1 db.migration anyways, it was the perfect time to agree on some conventions and implement them so that moving forward, we have a schema with as much consistency as possible.  This list should be added to if and when other conventions are agreed upon.


Schema

Tables

  • Table names should have underscores separating words, not camelcase, and should be all lowercase

  • Table names should not have prefixes unless it is to avoid conflict with reserved words, as was the case with "domain" which is prefixed with "md" for "master data"

  • Table names should be singular, not plural.  Here are some compelling arguments.

  • Tables should not have “entity” in the name, as this is redundant

Columns

  • Column names should be formatted like table names, i.e. with underscores and all lowercase

  • Foreign key column names should be of the format <referenced table>_<referenced column>. E.g. domain_id

Constraints

  • Foreign Key Constraint names?  fk_<source table>_<referenced table>_<referenced column>

Hibernate/ORM

The use of Hibernate, or other ORM, should be accompanied by a fair amount of skepticism, especially as it pertains to query performance.  In particular, relying on Hibernate to perform joins should be avoided except in limited situations.  Here are some guidelines:

  • Avoid FetchType.EAGER.  This is the biggest cause of hibernate getting a bad rap.  Realize that if you use EAGER, every query that retrieves an Object of that type will be doing AT LEAST one join.  It gets exponentially worse if EAGER is commonly used because other entities that are joined in may eagerly fetch other entities spanning many tables with one simple select query.  
    • What to do instead: Create them with FetchType.LAZY (the default), and for operations where you know you need related entities introduce alternate Repository methods that explicitly fetch that column via `join fetch` in the HQL.


Here are those situations where using EAGER is probably OK:

  • If the relationship is ALWAYS needed:
    • Example:  Say a user Role is a OneToMany relationship with a Permission entity.  There's a good chance that in all cases you're interested in the Role, you're also interested in the Permissions associated with that Role.  In this case, EAGER fetching this relationship may be OK. 
    • Counter Example:  Say you have a Person entity with a OneToOne relationship with the Address entity.  There are many cases where you would want to work with a Person object that you don't care about their Address.  In this case, EAGER fetching would be bad.  
  • ??? Add others, but honestly, I'd prefer we avoid it.