ChongQing Xiao
2004-Nov-30 16:16 UTC
question about rails regarding handling more than one table in one view
Hi, Everyone I am just starting to look at the rail and really like how it works. One question I can not figure out is how rails will solve the following problem. I have 3 tables Table 1 - Provider table ProvID Name Table 2 - Dept Table DeptId Name Table 3 - ProvWorkAtDept ProvID DeptID Table 3 is the relation table between provider and dept and one provider can work at more than one dept. Now if I want to have a view for provider which will let the user edit specific provider - name - dept lists (the user can enters multi dept for this particular provider) If I have a provider model, how the model will handle the dept lists since the dept lists is in a separate table, do I need to write Code to handle it or rails have some magic way to handle this problem. Also, How rails will handle master detail relation which is common in database application (such as order and order items)? Thanks a lot chong _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Jarkko Laine
2004-Nov-30 16:41 UTC
Re: question about rails regarding handling more than one table in one view
Hi Chong,
Rails has indeed magic to take care of both one-to-many and
many-to-many relationships. Please read
http://api.rubyonrails.org/classes/ActiveRecord/Associations/
ClassMethods.html which has an extensive explanation of how
associations work in Rails.
For your example:
class Provider > ActiveRecord::Base
has_and_belongs_to_many :depts
end
class Dept > ActiveRecord::Base
has_and_belongs_to_many :providers
end
class Order > ActiveRecord::Base
has_many :orderitems
end
class Orderitem > ActiveRecord::Base
belongs_to :order
end
HTH,
Jarkko
On 30.11.2004, at 18:16, ChongQing Xiao wrote:
> Hi, Everyone
>
> I am just starting to look at the rail and really like how it works.
>
> One question I can not figure out is how rails will solve the
> following problem.
>
> I have 3 tables
>
> Table 1 - Provider table
>
> ProvID
>
> Name
>
> Table 2 - Dept Table
>
> DeptId
>
> Name
>
> Table 3 – ProvWorkAtDept
>
> ProvID
>
> DeptID
>
> Table 3 is the relation table between provider and dept and one
> provider can work at more than one dept.
>
> Now if I want to have a view for provider which will let the user edit
> specific provider
>
> - name
>
> - dept lists (the user can enters multi dept for this particular
> provider)
>
> If I have a provider model, how the model will handle the dept lists
> since the dept lists is in a separate table, do I need to write
>
> Code to handle it or rails have some magic way to handle this problem.
>
> Also, How rails will handle master detail relation which is common in
> database application (such as order and order items)?
>
> Thanks a lot
>
> chong
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
--
Jarkko Laine
http://jlaine.net
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails
On Tue, 30 Nov 2004, Jarkko Laine wrote:> Rails has indeed magic to take care of both one-to-many > and many-to-many relationships. Please read > http://api.rubyonrails.org/classes/ActiveRecord/Associations/ > ClassMethods.html which has an extensive explanation of how > associations work in Rails.Is anybody working on even better magic to read this information from the database? If I declare: CREATE TABLE provider (provider_id int PRIMARY KEY) CREATE TABLE department (department_id int PRIMARY KEY) CREATE TABLE provider_department ( provider_id int NOT NULL REFERENCES provider ON DELETE CASCADE, department_id int NOT NULL REFERENCES department, PRIMARY KEY(provider_id, department_id) ) I should be able to code: class Provider < ActiveRecord::Base; end class Department < ActiveRecord::Base; end and the links should be put in place automatically. In addition, when I delete a provider, only "DELETE FROM provider WHERE provider_id = ?" should be issued; this will cascade automatically, and the deletion will be atomic. The same goes for deleting a department, except in this case, since I specified that I don''t want cascading deletes (because, say, this is a likely symptom of a program error or someone who doesn''t understand the database doing something mistaken), it should come back with an error telling me I need explicitly to go and deal with the providers. (This latter case is particularly useful in a situation where, say, every employee is a member of one and only one department, and you don''t want to delete employees when deleting a department, you want to make sure that every employee is reassigned before removing the now-empty department.) Re-specifying constraints in ruby that are already specified in the database is code duplication. cjs -- Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced by BIC CAMERA
> Re-specifying constraints in ruby that are already specified in the > database is code duplication.Which is why my approach (and recommendation) is not to specify it in the database, but only in Ruby code. I understand that this is considered blasphemy among some database people, though. And this advice only applies to an Application Database design -- not Integration Databases. As do all of my opinions on the division between database and object-model. -- David Heinemeier Hansson, http://www.basecamphq.com/ -- Web-based Project Management http://www.rubyonrails.org/ -- Web-application framework for Ruby http://macromates.com/ -- TextMate: Code and markup editor (OS X) http://www.loudthinking.com/ -- Broadcasting Brain
One benefit of doing it this way is that OO-Relational mismatch is better handled. It can be hard to map some OO design decisions (inheritence and complex associations come to mind) to relational constraints. Also, more complex constraints can be expressed in terms of the domain model and application logic, making it clearer to understand and maintain. For complex constraints, you could start doing triggers and stored procedures, but then you are creating complexity and maintainability issues with significant application logic spread over two languages and paradigms. Some examples are deleting a record when the references to it reach 0, or for certain types of users, a delete must go through an approval process. Or constraints being conditional on values (e.g. "released" or "published" documents). Nick David Heinemeier Hansson wrote:>> Re-specifying constraints in ruby that are already specified in the >> database is code duplication. > > > Which is why my approach (and recommendation) is not to specify it in > the database, but only in Ruby code. I understand that this is > considered blasphemy among some database people, though. > > And this advice only applies to an Application Database design -- not > Integration Databases. As do all of my opinions on the division > between database and object-model. > -- > David Heinemeier Hansson, > http://www.basecamphq.com/ -- Web-based Project Management > http://www.rubyonrails.org/ -- Web-application framework for Ruby > http://macromates.com/ -- TextMate: Code and markup editor (OS X) > http://www.loudthinking.com/ -- Broadcasting Brain > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On Thu, 2 Dec 2004, David Heinemeier Hansson wrote:> > Re-specifying constraints in ruby that are already specified in the > > database is code duplication. > > Which is why my approach (and recommendation) is not to specify it in > the database, but only in Ruby code. > ... > And this advice only applies to an Application Database design -- not > Integration Databases. As do all of my opinions on the division between > database and object-model.Well, I''ve been through this sort of argument before, and I can tell nobody here will be convinced. So I''ll just leave off with a few points here. 1. You still are re-writing code that''s already written (unless you''re using MySQL, of course). And it''s a sure thing that your code is not as extensively tested and as robust as that in an RDBMS. 2. You cannot change constraints on an existing database (except to remove them) unless you also write yet more application code to verify that the existing data follows those constraints. 3. I have yet to see--anywhere--an application database in production that was accessed only through the application code. The tempation to make quick fixes by running some SQL directly is just too good to pass up. (Usually for good reasons: it''s often far faster and easier to do a quick SQL fix, and in some cases, such as when you''re fixing the results of buggy application logic, there may be no way to do it through the application.) 4. Keeping even simple table-level constraints separated from the table definitions (the first in a ruby file, the second in an SQL file somewhere) violates the principle of keeping related things together. 5. In some (many?) cases when generating test data for your database you need to verify "by hand" that it meets all necessary constraints, since you''re using that test data to test the very classes that are implementing those constraints. Perhaps part of the issue is just that I''m dealing with rather more complex databases than many typical "web applications," (essentially, I''m modelling an entire business or business unit), and so I need more reliable constraints to manage the higher level of complexity. I don''t know how many database people really consider it "heresy" to specify constraints in application code, but certainly I don''t. I specify it in the application code when it''s easier to do there, and in the database code when it''s easier to do there. But foreign key constraints are so much easier to do in the DBMS that it mystifies me why anybody would want to do extra work to end up with something that''s less reliable. cjs -- Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced by BIC CAMERA
Curt, I, for one, actually agree with all you''ve said. I would point out, however, that you don''t necessarily need to duplicate these foreign key constraints in your application code. If the constraint fails when the ActiveRecord object tries to save the data, an ActiveRecord::StatementInvalid error will be raised. You can then take steps to handle this exception as necessary for your application. Granted, having AR do introspection on the constraints and checking them during the validation phase would provide a lot more power, but it''s not 100% necessary. On Thu, 9 Dec 2004 12:01:17 +0900 (JST), Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> wrote:> Well, I''ve been through this sort of argument before, and I can tell > nobody here will be convinced. So I''ll just leave off with a few points > here. > > 1. You still are re-writing code that''s already written (unless > you''re using MySQL, of course). And it''s a sure thing that your code > is not as extensively tested and as robust as that in an RDBMS. > > 2. You cannot change constraints on an existing database (except > to remove them) unless you also write yet more application code to > verify that the existing data follows those constraints. > > 3. I have yet to see--anywhere--an application database in > production that was accessed only through the application code. > The tempation to make quick fixes by running some SQL directly is > just too good to pass up. (Usually for good reasons: it''s often far > faster and easier to do a quick SQL fix, and in some cases, such as > when you''re fixing the results of buggy application logic, there may > be no way to do it through the application.) > > 4. Keeping even simple table-level constraints separated from the > table definitions (the first in a ruby file, the second in an SQL > file somewhere) violates the principle of keeping related things > together. > > 5. In some (many?) cases when generating test data for your > database you need to verify "by hand" that it meets all necessary > constraints, since you''re using that test data to test the very > classes that are implementing those constraints. > > Perhaps part of the issue is just that I''m dealing with rather more > complex databases than many typical "web applications," (essentially, > I''m modelling an entire business or business unit), and so I need more > reliable constraints to manage the higher level of complexity. > > I don''t know how many database people really consider it "heresy" to > specify constraints in application code, but certainly I don''t. I > specify it in the application code when it''s easier to do there, and > in the database code when it''s easier to do there. But foreign key > constraints are so much easier to do in the DBMS that it mystifies me > why anybody would want to do extra work to end up with something that''s > less reliable.-- Regards, John Wilger ----------- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don''t know," Alice answered. "Then," said the cat, "it doesn''t matter." - Lewis Carrol, Alice in Wonderland
>>>>> "Curt" == Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> 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
On Thu, 9 Dec 2004, Gleb Arshinov wrote:> * Garbage data in database is OK so long you design for it and it > never shows up in your UI.True. But designing for it is work. Probably the biggest thing that swayed me towards using constraints was that it saved me writing validiation code for what comes back from the database, not to mention having to remember to do outer joins in the right circumstances and some pretty horrible SQL syntax when I had to use them in multi-table joins. (I use lookup tables extensively, so it''s not unusual for me to do a six- or eight-way join.)> * Being able to express complex business logic in database language. > Often times FK constraints should only be enforced during certain > "milestones" not during transitions.This is in fact more easily done by adding or deleting the constraints from the DB, since you then don''t need to write code to validate existing data.> Consider this vs portability betwen different databases, using cryptic > database-specific languages...That''s, sadly, still an issue even for many so-called "Enterprise" databases, but it''s getting better. In both Oracle and PostgreSQL, for example, you can write stored procedures in languages such as Java or Perl. In PostgreSQL you can even write your stored procedures in Ruby! It''s hard to ask for more than that. :-)> ...and splitting your business logic.Impossible to avoid; your table design alone incorporates information about the structure of your business. Though I do admit, I tend to try to keep my SQL embedded in the rest of my application when I can. But once you decide that your SQL code (outside of the app) is code like any other, and you set up a test framework and unit and functional tests for it, it''s not nearly as bad as, say, having some of your application written in ruby and some in PHP.> * Performance and how it scales with data size.Yeah, that can definitely be a problem. But I have no real problem with removing FKs or otherwise changing things when it becomes necessary to secure the performance the application needs. But that''s only an excuse to use after you''ve proved that there''s a performance problem. cjs -- Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced by BIC CAMERA
On Thu, 9 Dec 2004, John Wilger wrote:> I would point out, however, that you don''t necessarily need to > duplicate these foreign key constraints in your application code. If > the constraint fails when the ActiveRecord object tries to save the > data, an ActiveRecord::StatementInvalid error will be raised.I was looking more for the automatic linking, so I could ask an Article object to for all of its associated Response objects, for example. But it''s just occured to me over the past week or two that we''re probably going about fixing this Object/Relational "impedence mismatch" in a completely backwards fashion. Why did relational databases replace hierarchial databases? Because pointers are a pain in the you-know-what. So why are we taking relational data and trying to turn it back into what is effectively a hierarchial database when we load it back in to memory? If we just worked with data in a relational way within our programs as well, we''d not only save our self time and trouble with in-memory data, but you could have essentially transparent movement of data between the RDBMS and memory. Basically, instead of this: all_locations = Array.new addresses.each { |address| if /@docomo.ne.jp$/.match(address) unless location_list.include?(address.location) all_locations << address.location end end } Make addresses a relation, and do just this: condition = DB::Like.new(/@docomo.ne.jp$/, address) addresses.restrict(condition).project(:location).distinct Bingo, done. And you don''t even have to think about whether it needs to be fetched from the database or not. cjs -- Curt Sampson <cjs-gHs2Wiolu3leoWH0uzbU5w@public.gmane.org> +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced by BIC CAMERA