DB Table Constraints using code or not! Best practices?

Rails
1

Hi guys,

although this is not Rails specific, I have built this Rails app and
designed the MySQL database with all constraints and indexes using MySQL
Workbench.

It is a pretty tool that allows to you to quickly build tables, create
associations etc… Of course this means no use of migration, which is
already bad practice per Rails I assume.

And I know you can create those constraints in code through ActiveRecord
associations attribute :dependent

None of the tutorials mention any table constraints or indexing when
building migrations:

  1. Is that even possible to set in migrations?

  2. Should we build those tables constraints and foreign keys straight in
    the Database? Or just simply keep it all loose and use :dependent
    instead, to cascade updates and deletes?

What are the best practices in Rails for that matter?

To answer FAQs:

  • Depends what you want to do:
    I want a clean table structure for my app, where I have dependencies
    with many tables. I need to cascade deletes and updates.

  • You can do them in both ways, build direct constraints or use
    ActiveRecord :depedent
    Yes I know, but what are the best practices? how to you guys do it and
    why? scalability is one of the aims as well. I don’t want Constraints on
    the tables to be in the way later.

Please let me know from your experience.

Thanks

2

ActiveRecord
the Database? Or just simply keep it all loose and use :dependent
ActiveRecord :depedent
Yes I know, but what are the best practices? how to you guys do it
and
why? scalability is one of the aims as well. I don’t want
Constraints on
the tables to be in the way later.

Please let me know from your experience.

Should be an interesting thread…

Yes, you can set those things in your migrations… if Rails doesn’t
support your DB natively you can always just write out the SQL directly.

As for where to handle cascaded deletes… as long as it’s crystal
clear to your team which way you’re going to roll you’ll probably be
fine either way.

That said… will there ever be a time where you want more than a
simple cascaded delete? Where you need to instantiate and destroy the
object? Perhaps because that object has data stored in the file
system or on S3? Or because you have counters somewhere that need to
be decremented appropriately? Or maybe you have business logic such
that you can’t delete an object until it’s children have been
reassigned. Are you willing to dive into DB specific logic to make
that work?

If your app is pure Rails and will only ever be that way, I’d be
inclined to keep things within Rails to keep the database a bit more
agnostic, to help keep code in one place, and so you can pick up some
of the Rails association magic if you want it.

However, if your app will also be accessed from other systems and you
need a central place to enforce rules, then the database has to have it.

Not really an answer one way or the other… just some things to
consider.

-philip

3

On Wed, Nov 11, 2009 at 2:43 PM, Bachir El khoury
[email protected] wrote:

the Database? Or just simply keep it all loose and use :dependent
ActiveRecord :depedent
Yes I know, but what are the best practices? how to you guys do it and
why? scalability is one of the aims as well. I don’t want Constraints on
the tables to be in the way later.

Please let me know from your experience.

FWIW, I use the foreign_key_migrations plugin which defines FK’s in my
migrations for me. I do this because:

  1. I don’t trust ActiveRecord to ensure my relations are sane
  2. I don’t always use ActiveRecord to interact with my DB

–
Aaron T.
http://synfin.net/
http://tcpreplay.synfin.net/ - Pcap editing and replay tools for Unix &
Windows
Those who would give up essential Liberty, to purchase a little
temporary
Safety, deserve neither Liberty nor Safety.
– Benjamin Franklin
“carpe diem quam minimum credula postero”

4

Aaron T. wrote:

FWIW, I use the foreign_key_migrations plugin which defines FK’s in my
migrations for me. I do this because:

  1. I don’t trust ActiveRecord to ensure my relations are sane
  2. I don’t always use ActiveRecord to interact with my DB

Marnen Laibow-Koser wrote:
I do likewise, for the same reasons. I understand there’s now a similar
plugin called Foreigner, but I’ve never used it.

Add another +1 for the foreign_key_migrations plugin.
If some other person, or some other application will ever connect to
your database (happens all the time here at work), defend it at the db
level.

5

Thanks alot guys for your input and suggestions.

Good points.

After researching about PostgreSQL which I might be using for the
project, it is full of features that the db server can handle and
automate rather than rely on Rails.

I will stick with the database relations then, it never fails but just
thought of getting a different perspective.

thanks again

6

Aaron T. wrote:

On Wed, Nov 11, 2009 at 2:43 PM, Bachir El khoury
[email protected] wrote:

the Database? Or just simply keep it all loose and use :dependent
ActiveRecord :depedent
�Yes I know, but what are the best practices? how to you guys do it and
why? scalability is one of the aims as well. I don’t want Constraints on
the tables to be in the way later.

Please let me know from your experience.

FWIW, I use the foreign_key_migrations plugin which defines FK’s in my
migrations for me. I do this because:

  1. I don’t trust ActiveRecord to ensure my relations are sane
  2. I don’t always use ActiveRecord to interact with my DB

I do likewise, for the same reasons. I understand there’s now a similar
plugin called Foreigner, but I’ve never used it.

–
Aaron T.
http://synfin.net/
http://tcpreplay.synfin.net/ - Pcap editing and replay tools for Unix &
Windows
Those who would give up essential Liberty, to purchase a little
temporary
Safety, deserve neither Liberty nor Safety.
– Benjamin Franklin
“carpe diem quam minimum credula postero”

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

7

Bachir El khoury wrote:

Thanks alot guys for your input and suggestions.

Good points.

After researching about PostgreSQL which I might be using for the
project,

Good choice. With PostgreSQL, you get referential integrity and a fast
storage engine.

it is full of features that the db server can handle and
automate rather than rely on Rails.

I will stick with the database relations then, it never fails but just
thought of getting a different perspective.

You probably want to have the AR validations as well – it makes error
handling a bit easier.

thanks again

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]