Rails - Nov 2009 - DB Table Constraints using code or not! Best practices?

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
-- 
Posted via http://www.ruby-forum.com/.

> 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.
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

On Wed, Nov 11, 2009 at 2:43 PM, Bachir El khoury
<rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:
>
> 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.
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 Turner
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"

Aaron Turner wrote:
> On Wed, Nov 11, 2009 at 2:43 PM, Bachir El khoury
> <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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 Turner
> 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
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

Aaron Turner 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.
-- 
Posted via http://www.ruby-forum.com/.

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
-- 
Posted via http://www.ruby-forum.com/.

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
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.