Hello folks, I use to think that a relational databases without foreign keys constraints enforced strictly by the RDBMS were no good, piece of crap, data junk. However, I''ve also learned the hard way that FKs constraints imposes serious difficulties when moving data around, specially between different DBs, or for loading test data or for upgrading DBs schemas, or for porting DBs and other special data mangling outside the well-bevahed application CRUD functionality. So, I was thinking, why is FK contraints good? For one thing, they improve performance by indexing the FK fields (at least in the good RDMSes), but that can be done independently. And then, the most obvious reason and most beneficial, they enforce data integrity, ie, data don''t get lost because you didn''t set the FK the right value. But, as I said before this doesn''t come for free. So, if we use a good relational data mapper such as ActiveRecord that handles all FK manipulation, and in its own way, enforces data integrity, are FK constraints worth the pain? What''s your take on that? rgds Demetrius http://dema.ruby.com.br/
Good question! Personally I don''t use FKs, triggers, views or stored procedures at all. In the applications I write i have full control over the code and so I know that no garbage makes it into the DB. These facilities are there to validate the data you to the RDMS but frankly, ruby is much better at it. Because my ruby code is the single point of entry for data to the DB i much prefer the comfort of ruby for complex validation to whatever my DB might offer me. FKs and even stored procedures make a lot of sense in systems which are accessed by more then one client. However that is a design problem all by itself in my eyes. A better solution for this are web services and REST. FKs can be applied as a optimization ( sanity, not performance ) for a production database. That''s an idea I have been toying with... There is a way to have the best of both worlds. AR could parse the FK data from the schema and add the proper validations to the runtime code. But that''s something for the future :) On 6/27/05, Demetrius Nunes <demetrius-fDpYTK8McCzCdMRJFJuMdgh0onu2mTI+@public.gmane.org> wrote:> Hello folks, > > I use to think that a relational databases without foreign keys > constraints enforced strictly by the RDBMS were no good, piece of crap, > data junk. > > However, I''ve also learned the hard way that FKs constraints imposes > serious difficulties when moving data around, specially between > different DBs, or for loading test data or for upgrading DBs schemas, or > for porting DBs and other special data mangling outside the well-bevahed > application CRUD functionality. > > So, I was thinking, why is FK contraints good? For one thing, they > improve performance by indexing the FK fields (at least in the good > RDMSes), but that can be done independently. And then, the most obvious > reason and most beneficial, they enforce data integrity, ie, data don''t > get lost because you didn''t set the FK the right value. But, as I said > before this doesn''t come for free. > > So, if we use a good relational data mapper such as ActiveRecord that > handles all FK manipulation, and in its own way, enforces data > integrity, are FK constraints worth the pain? > > What''s your take on that? > > rgds > Demetrius > http://dema.ruby.com.br/ > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Tobi http://www.snowdevil.ca - Snowboards that don''t suck http://typo.leetsoft.com - Open source weblog engine http://blog.leetsoft.com - Technical weblog
On 6/27/05, Demetrius Nunes <demetrius-fDpYTK8McCzCdMRJFJuMdgh0onu2mTI+@public.gmane.org> wrote:> Hello folks, > > I use to think that a relational databases without foreign keys > constraints enforced strictly by the RDBMS were no good, piece of crap, > data junk. > > However, I''ve also learned the hard way that FKs constraints imposes > serious difficulties when moving data around, specially between > different DBs, or for loading test data or for upgrading DBs schemas, or > for porting DBs and other special data mangling outside the well-bevahed > application CRUD functionality.Most ''good'' RDBMSs as you call them will also have the capability to disable various constraints, which you can do during load or what not. Or you can structure your load such that it does not violate the constraints, which IMHO is a better choice.> So, I was thinking, why is FK contraints good? For one thing, they > improve performance by indexing the FK fields (at least in the good > RDMSes), but that can be done independently. And then, the most obvious > reason and most beneficial, they enforce data integrity, ie, data don''t > get lost because you didn''t set the FK the right value. But, as I said > before this doesn''t come for free. > > So, if we use a good relational data mapper such as ActiveRecord that > handles all FK manipulation, and in its own way, enforces data > integrity, are FK constraints worth the pain?If you have a small-ish database, and your Rails/ActiveRecord app is the only thing using it, this may be possible. But in a bigger world with bigger data, it becomes impossible to let the application completely manage the integrity of the data. This is especially true if you have multiple different applications working with the same data, possibly written by different teams or even using different languages. The chances of the apps enforcing different or incorrect rules increase tremendously.> > What''s your take on that?Foreign Key constraints are definitely a good thing.> rgds > Demetrius > http://dema.ruby.com.br/ > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >Jason
This debate can almost be boiled down solely to whether you''re using an application or integration database design as described by Martin Fowler on http://martinfowler.com/bliki/DatabaseStyles.html. Application databases answer to one application where it''s possible (and attractive) to let the application manage integrity. With integration databases, you need to stuff the database with all that funky stuff that makes PostgreSQL and similar "strong" databases a good choice. If you''re building a new application from scratch, I''d certainly recommend going the application database design way. And do integration through web services or other through-the-app ways. That''s how I enjoy life without FKs and using MySQL. -- David Heinemeier Hansson http://www.loudthinking.com -- Broadcasting Brain http://www.basecamphq.com -- Online project management http://www.backpackit.com -- Personal information manager http://www.rubyonrails.com -- Web-application framework
On 6/28/05, David Heinemeier Hansson <david.heinemeier-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> This debate can almost be boiled down solely to whether you''re using > an application or integration database design as described by Martin > Fowler on http://martinfowler.com/bliki/DatabaseStyles.html.This is an excellent point, I''m a huge fan of the application database approach and I''d *strongly* suggest any new applications do the same. As far as foreign keys go, before abandoning them entirely you should check that your database doesn''t use them in its query planning. I know that some sybase products I worked with would be significantly faster on inner and outer joins on foreign keys. But, triggers, stored procedures, views and all that? overrated and only enjoyed by the ''DBA teamsters''. -- Cheers Koz
David Heinemeier Hansson wrote:> If you''re building a new application from scratch, I''d certainly > recommend going the application database design way. And do > integration through web services or other through-the-app ways. That''s > how I enjoy life without FKs and using MySQL.I agree with this if you see the application as the most valuable part, with the db just as a simple datastore. But, if you see the db (the data) as the most valuable part (holding all your customers/historical/warehouse data), with the application just as a means to access it, I feel a bit helpless without the safetynet of foreign keys, grants and views, and I would have to disagree with the above. In my field (which is mainly ERP), databases tend to have a longer life than the app that''s connecting to it. It''s a safe feeling to know that I can change apps/interfaces if needed, without having to worry about data integrity in the db. Which brings me to a more pragmatic reason why I like to keep foreign keys in the db: testability. With foreign keys, rules and not null constraints (except for the way not null constraints are implemented in mysql unfortunately), it''s so much easier to find out if you made mistakes in your models (mainly in the associations). Especially when still being in the learning phase of Activerecord, the foreign key troubles mentioned in the parent post have made it easier for me to understand the way AR handles its associations. I also have the feeling that having foreign keys in the db makes me need less unit tests to make sure my models are correct, but I can''t say for sure. The problem with loading fixtures can on one hand be a bit annoying (you have to load the fixtures in the right order) but on the other hand also help you check that your database is correct (and that you solved the relationships between tables (chicken-egg problems) correctly). Conclusion: personally, I would only let go of foreign keys and other constraints if a) the db is only accessed by rails (as stated in the parent posts) b) the data in the db does not have a long lifetime and/or is not valuable compared to the application itself c) the db scheme is not complicated (I think less than 15 tables, with less than 5 many to many relationships or so) d) there is only a low chance of simultaneous read/write access on the same table (actually I mention this because I don''t know how well AR handles/atomizes transactions) Best regards, Bas
On Tue, 2005-06-28 at 08:49 +1200, Michael Koziarski wrote:> On 6/28/05, David Heinemeier Hansson <david.heinemeier-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > This debate can almost be boiled down solely to whether you''re using > > an application or integration database design as described by Martin > > Fowler on http://martinfowler.com/bliki/DatabaseStyles.html. > > This is an excellent point, I''m a huge fan of the application > database approach and I''d *strongly* suggest any new applications do > the same. > > As far as foreign keys go, before abandoning them entirely you should > check that your database doesn''t use them in its query planning. I > know that some sybase products I worked with would be significantly > faster on inner and outer joins on foreign keys. > > But, triggers, stored procedures, views and all that? overrated and > only enjoyed by the ''DBA teamsters''.I think a relational database is more than just a way to lump data on disks; it is a way to ''manage'' your data to ensure ''correctness''. Because of this, I like to assume that ActiveRecord as an extension (or re-invention) of the DBMS; validating data for correctness has always been a core principle in thinking about relational databases. And FK constraints are no different; they are helpful as a matter of documentation (I like to keep database-y things in the database), along with the extra support it gives you (e.g. on delete/update do-something) I think a novel and powerful feature in ActiveRecord is to have all database constraints, be it referential integrity constraints, ''CHECK'' constraints, NOT NULL constraints, etc. be introspected on to use for validations. If I do an insert, the database constraint fires off telling us if it is okay or not to do the insert. Anyway, I must be a DBA teamster, without any real credentials to be a part of that union! I like triggers, stored procedures and views. I''d also like complete, unfettered access to them in my application code. (I''d go on to say that I would rather use something that relies on relational algebra + constraint tools than the current approach, but that is for a later project to think about. :-) Brian
On 6/28/05, Michael Champanis <michael-MXk1+JRFB8SsTnJN9+BGXg@public.gmane.org> wrote:> > But, triggers, stored procedures, views and all that? overrated and > > only enjoyed by the ''DBA teamsters''. > > I find views very useful in that I don''t have to have huge find_by_sql > statements sitting around in my controller - they can just be > find_by_sql "select * from myview", and myview can have as many joins > and esoteric sql commands as it needs to. > > It all depends on your application - I''m working on a financial program > for a client, and there are a lot of commission payments that are > calculated using stored procedures, simply because all they need is a > date parameter from rails, and the rest of the data is all in the db > already - I don''t think there''s any point in having AR slow everything > down when it''s not needed.I share your preference for keeping as much logic as possible in the database for this type of application. I''m working on a database for an investment company and this means I simply won''t sleep well at night when data integrity depends solely on the management application. Also, I tend to think that stored procedures and views make my database (and the layers above) easier to understand. For this application, I haven''t created a single model yet that represents an ordinary table. I have heavily integrated i18n in the DB, for example. Each name or message in any table references a record in a table with messages that can easily be translated using another table. It is extremely nice to offer to Rails nice views such as ''my_table_eng'' with the localized messages for my_table included. Another nice thing I did with views is having a view called messages_to_translate. This was a stored procedure first, but worked better as a view. To get a list of all messages that need (re)translation into Dutch, all I have to do is SELECT * FROM messages_to_translate WHERE local_alpha3t_lang_code = ''nld'' Now if only inserting records in these views would work in Rails ... - Rowan -- Morality is usually taught by the immoral.
> But, triggers, stored procedures, views and all that? overrated and > only enjoyed by the ''DBA teamsters''.I find views very useful in that I don''t have to have huge find_by_sql statements sitting around in my controller - they can just be find_by_sql "select * from myview", and myview can have as many joins and esoteric sql commands as it needs to. It all depends on your application - I''m working on a financial program for a client, and there are a lot of commission payments that are calculated using stored procedures, simply because all they need is a date parameter from rails, and the rest of the data is all in the db already - I don''t think there''s any point in having AR slow everything down when it''s not needed. Michael
On 6/27/05, Tobias Luetke <tobias.luetke-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Good question! > > Personally I don''t use FKs, triggers, views or stored procedures at all. > In the applications I write i have full control over the code and so I > know that no > garbage makes it into the DB. These facilities are there to validate > the data you > to the RDMS but frankly, ruby is much better at it. > Because my ruby code is the single > point of entry for data to the DB i much prefer the comfort of ruby > for complex validation to whatever my DB might offer me. > FKs and even stored procedures make a lot of sense in systems which > are accessed by more then one client. However that is a design problem > all by itself in my eyes. > A better solution for this are web services and REST. > > FKs can be applied as a optimization ( sanity, not performance ) for a > production database. That''s an idea I have been toying with... > > There is a way to have the best of both worlds. AR could parse the FK > data from the schema and add the proper validations to the runtime > code. But that''s something for the future :) > > On 6/27/05, Demetrius Nunes <demetrius-fDpYTK8McCzCdMRJFJuMdgh0onu2mTI+@public.gmane.org> wrote: > > Hello folks, > > > > I use to think that a relational databases without foreign keys > > constraints enforced strictly by the RDBMS were no good, piece of crap, > > data junk. > > > > However, I''ve also learned the hard way that FKs constraints imposes > > serious difficulties when moving data around, specially between > > different DBs, or for loading test data or for upgrading DBs schemas, or > > for porting DBs and other special data mangling outside the well-bevahed > > application CRUD functionality. > > > > So, I was thinking, why is FK contraints good? For one thing, they > > improve performance by indexing the FK fields (at least in the good > > RDMSes), but that can be done independently. And then, the most obvious > > reason and most beneficial, they enforce data integrity, ie, data don''t > > get lost because you didn''t set the FK the right value. But, as I said > > before this doesn''t come for free. > > > > So, if we use a good relational data mapper such as ActiveRecord that > > handles all FK manipulation, and in its own way, enforces data > > integrity, are FK constraints worth the pain? > > > > What''s your take on that? > > > > rgds > > Demetrius > > http://dema.ruby.com.br/ > > > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > -- > Tobi > http://www.snowdevil.ca - Snowboards that don''t suck > http://typo.leetsoft.com - Open source weblog engine > http://blog.leetsoft.com - Technical weblog > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >Would apply the same thinking to larger models with many developers working on it, with multiple deployments over time? What scares me is data integrity "leaks", where data is damanged in the database with relationships and integrity being lost due to errors in the application logic. While the ORM can do some of this, I think it''s a few years before the theory and technology catch up to the robustness of the RDMS capabilities. On the other hand, a complex OO domain model makes it very hard to specify constraints via the RDMS. Cheers, Nick -- Nicholas Van Weerdenburg