[Rails] Re: Duplicating Constraints

[Gleb Arshinov]

>>>>> "Curt" == Curt Sampson <cjs at cynic.net> writes:

<snip>

    Curt> Perhaps part of the issue is just that I'm dealing with
    Curt> rather more complex databases than many typical "web
    Curt> applications," (essentially, I'm modelling an entire
    Curt> business or business unit), and so I need more reliable
    Curt> constraints to manage the higher level of complexity.

    Curt> I don't know how many database people really consider it
    Curt> "heresy" to specify constraints in application code, but
    Curt> certainly I don't. I specify it in the application code when
    Curt> it's easier to do there, and in the database code when it's
    Curt> easier to do there. But foreign key constraints are so much
    Curt> easier to do in the DBMS that it mystifies me why anybody
    Curt> would want to do extra work to end up with something that's
    Curt> less reliable.


I used to be a big believer in using FK constraints.  I've implemented
FK constraint support for a commercial RDBMS, so how could I not like
them :-) Later, I ended up working for Siebel (~2.5K tables in
schema), which does not use FK constraints at all.  It works
perfectly, actually it works a lot better than having FK constraints.
I am not entirely conviced FK constraints are even viable for
application of this complexity.  Consider:

* Garbage data in database is OK so long you design for it and it
  never shows up in your UI.  In a parent/child relationship JOIN will
  not show children when parent is gone, while OUTER JOIN will -- you
  pick one to correspond to the business logic.

* Being able to express complex business logic in database language.
  Often times FK constraints should only be enforced during certain
  "milestones" not during transitions.  Consider this vs portability
  betwen different databases, using cryptic database-specific
  languages, and splitting your business logic.

* Performance and how it scales with data size.

At the same time there are valid reasons to use FK constraints as
you've desribed, mainly if you really do have to access your data
bypassing the app.  As most of these things are, it's a compromise.

Gleb