My people are the greatest asset of my company
— most CEOs

Your data is the greatest asset of your company
— Dan Chak

 

 

Enterprise Rails

  • Highly opinionated
     
  • Not that much Rails stuff
     
  • How to use database-level validations, triggers, and integrity checks with your web application
     
  • (Possibly overkill)

By Dan Chak (2009)

Worked example

movie reservation site

Three tables: movies, movie_showtimes, theatres with unnormalized columns

Initial (naïve) Schema

https://danchak99.files.wordpress.com/2014/07/er_0501.jpg

Chapter 5

  • Put validations in the data definition language (DDL)
  • Write unit tests in Python to confirm you cannot sidestep validations from your app
  • Add application-level validations like normal
  • Now you are a fortress

Step zero for Data Fortess

Same schema

New stuff (constraints) in bold purple

create sequence movies_id_seq;
create table movies (
   id integer,


   name varchar(256),


   length_minutes integer,


   rating varchar(8),


   primary key (id)
);
One table; movies; with columns id, name, length_minutes, rating
create sequence movies_id_seq;
create table movies (
   id integer not null
      default nextval('movies_id_seq'),

   name varchar(256) not null unique
      check (length(name) > 0),
   length_minutes integer not null
    check (length_minutes > 0),
   rating varchar(8) not null
    check (rating in ('Unrated', 'G', 'PG', 'PG-13', 'R', 'NC-17')),

   primary key (id)
);

Caveat

And now you have created the possibility of drift between your database validations and your codebase.

*Caveat from my manager:

* Response:

The database must *always* be the source of truth. The instant any other user than the web app can touch the database you are hosed without db-level constraints.

Chapter 6

Third normal form (Don't Repeat Yourself)

  • Rule of thumb to refactor your data model:
    • If a column relates to another column that's not the primary key
    • Then those paired columns should be factored out
  • Congratulations you are DRY

Example for movies

If you add a 'rating description' column

  • Never want a different description for the same rating

Caveat

Multiple joins add friction when coding and may increase query time

*Caveat about performance:

* Response:

True but you can add a view and triggers to make it appear like a non-normalized table for developers.

^ The trade-off is work in the database in order to maintain data integrity. The question is whether it's worth it for your use case.

Chapter 7

Domain data (knowledge belongs in-DB)

  • Rule of thumb for domain data:
    • If you are hard-coding strings
    • Or rows are slow-moving and known a priori
    • Then this knowledge should be pre-loaded in the database
  • Congratulations your domain knowledge is fortified

Examples

Things that rarely ever change

  • Don't hard-code a list into the codebase, put it in the database
  • Preloading (e.g. for zip codes) give you validity checks for free:
    • "Zip code not valid. Please enter another"

Caveat

Now every time you want a constant you must query the database

*Caveat about performance:

* Response:

True but you can query it once and memoize.

Chapter 8

Domain-key Normal Form
(composite keys)

  • Rule of thumb to add composite keys:
    • If it is a PITA to get at your columns after refactoring to 3rd Normal Form
    • Then add back those columns as a composite key
  • Congratulations you have best of both worlds

Examples

Auditoriums in a movie theatre: want

theatre_id

Examples

Deleted primary key id and made             +                                the key

theatre_id
room

Caveat

WTF if it's a string why? Slow-ish. Plus if you ever need to change the key it's a beast

*Caveat about using domain keys:

* Response:

Agree! Why not keep the original ID key and add a unique constraint on the domain key columns?

Conclusion:

Your mileage may vary

But IMO it is worth it to consider pushing logic, validations, everything you can down into the database. Worth a read.

Enterprise Rails

  • Highly opinionated
     
  • Not that much Rails stuff
     
  • How to use database-level validations, triggers, and integrity checks with your web application
     
  • (Possibly overkill)

By Dan Chak (2009)

Also slides by

https://slides.com

enterprise-data

By Tanya Schlusser

Made with Slides.com

enterprise-data

Dan Chak's 'Enterprise Rails' has a ton of insight applicable to all web apps, not just Rails. Chapter 4-8 are relevant for the Data SIG: how should we (re)structure a website's database, moving it from "startup grade" to "enterprise grade"? This talk attempts to distill the most important points from those chapters into 10 minutes.

  • 842

More from Tanya Schlusser