Hi all - I''d like to implement a lookup table in my app that contains some reference data, just some status codes and their descriptions. What''s the most appropriate approach according to the "rails way" of doing things? Would I just implement a has_many relationship? Let''s say these are Order status codes, would the StatusCode have many Orders? This seems right from a database POV (normalizing the code and description into a separate table), but sort of goofy from a coding POV. "Order belongs_to :order_status_code" seems a little off to me. It would be nice if I could somehow automatically have the status description automatically be added as a read-only attribute to the Order class. Suppose the above approach is the correct one. Would I still use the standard id column as my primary key, or just use the status code as I normally would in a non-rails app? It seems like it would make sense to do the latter, but would I regret going against rails conventions when it is just as easy to go along with it? Thanks for any insight into this. I''m having a blast learning Rails. Ken
Just put a hash in your Order model, together with an accessor function
if you want one:
class Order < ActiveRecord::Base
belongs_to :customer
has_many :line_item
ORDER_STATUS = {
''OP'' => ''Open'',
''SH'' => ''Shipped'',
''CN'' => ''Cancelled''
}.freeze # freeze to make this hash constant
def status_description {
return ORDER_STATUS[self.status]
}
end
--
Posted via http://www.ruby-forum.com/.
On 5/10/06, Kenneth Liu <ken.liu.pub@gmail.com> wrote:> Hi all - > > I''d like to implement a lookup table in my app that contains some > reference data, just some status codes and their descriptions. What''s > the most appropriate approach according to the "rails way" of doing > things? Would I just implement a has_many relationship? Let''s say > these are Order status codes, would the StatusCode have many Orders? > This seems right from a database POV (normalizing the code and > description into a separate table), but sort of goofy from a coding > POV. "Order belongs_to :order_status_code" seems a little off to me. > It would be nice if I could somehow automatically have the status > description automatically be added as a read-only attribute to the Order > class. > > Suppose the above approach is the correct one. Would I still use the > standard id column as my primary key, or just use the status code as I > normally would in a non-rails app? It seems like it would make sense to > do the latter, but would I regret going against rails conventions when > it is just as easy to go along with it? > > Thanks for any insight into this. I''m having a blast learning Rails. >Mick''s suggestion is good. Another way is to turn the status code into a regular Ruby class that you can add additional methods and data to, while still keeping it as a single column in your db. Assuming your table has a status_code column... class Status def initialize(code) @code = code end def code # something to return a useful status from the numeric value in the DB end end class Order < ActiveRecord::Base composed_of :status, :mapping => %w(status_code status) end
I personally like to use separate tables for lookup tables, rather than hash or class oriented approaches. The big reason: you can edit the values in the lookup table down the road, even if its only from something as simple as a generated scaffold. I think the code is a terrible place to define what possible values there should be for something like status. -- Posted via http://www.ruby-forum.com/.
I think these methods work well unless you want the lookup values to be easy for someone to edit (e.g. admin). In that case, the best solution I have come up with is to create a table for each lookup and use the has_many relationship. I saw a writeup somewhere where someone used a single lookup table for all lookups in their application, but that requires a column to distinguish which lookup each entry belongs to and seems little messy to me. Of course having a dozen lookup tables also feels messy at times. On 5/10/06, Wilson Bilkovich <wilsonb@gmail.com> wrote:> > On 5/10/06, Kenneth Liu <ken.liu.pub@gmail.com> wrote: > > Hi all - > > > > I''d like to implement a lookup table in my app that contains some > > reference data, just some status codes and their descriptions. What''s > > the most appropriate approach according to the "rails way" of doing > > things? Would I just implement a has_many relationship? Let''s say > > these are Order status codes, would the StatusCode have many Orders? > > This seems right from a database POV (normalizing the code and > > description into a separate table), but sort of goofy from a coding > > POV. "Order belongs_to :order_status_code" seems a little off to me. > > It would be nice if I could somehow automatically have the status > > description automatically be added as a read-only attribute to the Order > > class. > > > > Suppose the above approach is the correct one. Would I still use the > > standard id column as my primary key, or just use the status code as I > > normally would in a non-rails app? It seems like it would make sense to > > do the latter, but would I regret going against rails conventions when > > it is just as easy to go along with it? > > > > Thanks for any insight into this. I''m having a blast learning Rails. > > > > Mick''s suggestion is good. Another way is to turn the status code > into a regular Ruby class that you can add additional methods and data > to, while still keeping it as a single column in your db. > > Assuming your table has a status_code column... > > class Status > def initialize(code) > @code = code > end > > def code > # something to return a useful status from the numeric value in the DB > end > end > > class Order < ActiveRecord::Base > composed_of :status, :mapping => %w(status_code status) > end > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060510/a63448fe/attachment-0001.html
On 5/10/06, Bryan Duxbury <bryan.duxbury@gmail.com> wrote:> I personally like to use separate tables for lookup tables, rather than > hash or class oriented approaches. The big reason: you can edit the > values in the lookup table down the road, even if its only from > something as simple as a generated scaffold. I think the code is a > terrible place to define what possible values there should be for > something like status. >Agreed, definitely, when you need to edit the codes frequently, or without restarting the app. I feel that full-on tables with models are overkill for simple status columns, though. For example, what if you only have: 0 => AWAITING_REVIEW 1 => ACTIVE 2 => SOMETHING_ELSE 3 => CLOSED ..or something similar. Four rows maybe wouldn''t be worth a whole table, but you need a numeric code for faster indexing, etc, etc.
Thanks everyone for your replies! They''re all very well thought out. I think I''ll go with the table and has_many approach, simply because it will be easier for my client to edit the descriptions. Another drawback with the pure-ruby approaches is that not storing the descriptions in the database will make it difficult for other applications (say a reporting app) to use the data, as they won''t have the descriptions available. (unless you want to get all philosophical and say that other applications shouldn''t access the data directly and you should use web services) Now the question is, is it ok to use the code as the primary key in the lookup table? Are there any gotchas to using something besides the automagical "id" column? Ken Wilson Bilkovich wrote:> On 5/10/06, Bryan Duxbury <bryan.duxbury@gmail.com> wrote: >> I personally like to use separate tables for lookup tables, rather than >> hash or class oriented approaches. The big reason: you can edit the >> values in the lookup table down the road, even if its only from >> something as simple as a generated scaffold. I think the code is a >> terrible place to define what possible values there should be for >> something like status. >> > > Agreed, definitely, when you need to edit the codes frequently, or > without restarting the app. > > I feel that full-on tables with models are overkill for simple status > columns, though. > For example, what if you only have: > 0 => AWAITING_REVIEW > 1 => ACTIVE > 2 => SOMETHING_ELSE > 3 => CLOSED > > ..or something similar. Four rows maybe wouldn''t be worth a whole > table, but you need a numeric code for faster indexing, etc, etc. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
Why bother trying to use a different key format? It''ll "just work" with autonumbering ids, and you can still put an index on that field for faster lookups if you need it. If you''re worried about having to do two queries to get an item and its status, use the preloading feature that AR provides (:include => ...). -- Posted via http://www.ruby-forum.com/.
Hey, I wrote a plugin for this very thing. Rather than has_many and belongs_to you have has_enumerated and acts_as_enumerated. It gives you extra goodness like caching and being able to write code that looks (imho) better: class Foo < ActiveRecord::Base has_enumerated :status end class Status < ActiveRecord::Base acts_as_enumerated, :order => ''position ASC'' end foo = Foo.new() foo.status = :open foo.status = Status[:open] You can get details for the plugin at http:// www.agilewebdevelopment.com/plugins/show/4 It allows separate tables for each enumeration or you can put them all in the same table (it uses STI in that case). It also has ''virtual'' enumeration classes - so if you have 20 different enumeration types you don''t need to have 20 different files in your models directory. The only thing which might make it unsuitable for you is that it caches the values in-memory only - and I make it purposefully hard to change the record values as a result (don''t want 3 different fcgi listeners getting out of sync). So, if you plan on frequently changing the values for Status records then you should look at another solution. Personally, I only change these sorts of records (like status codes) during a deploy anyhow. Hope this helps, Trevor -- Trevor Squires http://somethinglearned.com On 10-May-06, at 11:56 AM, Kenneth Liu wrote:> Thanks everyone for your replies! They''re all very well thought out. > > I think I''ll go with the table and has_many approach, simply > because it will be easier for my client to edit the descriptions. > > Another drawback with the pure-ruby approaches is that not storing > the descriptions in the database will make it difficult for other > applications (say a reporting app) to use the data, as they won''t > have the descriptions available. (unless you want to get all > philosophical and say that other applications shouldn''t access the > data directly and you should use web services) > > Now the question is, is it ok to use the code as the primary key in > the lookup table? Are there any gotchas to using something besides > the automagical "id" column? > > Ken > > Wilson Bilkovich wrote: >> On 5/10/06, Bryan Duxbury <bryan.duxbury@gmail.com> wrote: >>> I personally like to use separate tables for lookup tables, >>> rather than >>> hash or class oriented approaches. The big reason: you can edit the >>> values in the lookup table down the road, even if its only from >>> something as simple as a generated scaffold. I think the code is a >>> terrible place to define what possible values there should be for >>> something like status. >>> >> >> Agreed, definitely, when you need to edit the codes frequently, or >> without restarting the app. >> >> I feel that full-on tables with models are overkill for simple status >> columns, though. >> For example, what if you only have: >> 0 => AWAITING_REVIEW >> 1 => ACTIVE >> 2 => SOMETHING_ELSE >> 3 => CLOSED >> >> ..or something similar. Four rows maybe wouldn''t be worth a whole >> table, but you need a numeric code for faster indexing, etc, etc. >> _______________________________________________ >> Rails mailing list >> Rails@lists.rubyonrails.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails
I have looked at your plugin a couple of times, but always stayed away because I thought it would make it difficult to change the lookup values. But I think you are right in noting that for many applications the lookup values won''t change that often. It would be ideal if there was a command you could issue to lock the application until changes to the lookups through an admin panel were complete. This way you could schedule your update, lock the application to prevent users from getting incorrect lookups during the update, make the changes through your application admin views, and then unlock the application for normal use again. Do you have any views on this idea? On 5/10/06, Trevor Squires <trevor@protocool.com> wrote:> > Hey, > > I wrote a plugin for this very thing. Rather than has_many and > belongs_to you have has_enumerated and acts_as_enumerated. > > It gives you extra goodness like caching and being able to write code > that looks (imho) better: > > class Foo < ActiveRecord::Base > has_enumerated :status > end > > class Status < ActiveRecord::Base > acts_as_enumerated, :order => ''position ASC'' > end > > foo = Foo.new() > foo.status = :open > foo.status = Status[:open] > > You can get details for the plugin at http:// > www.agilewebdevelopment.com/plugins/show/4 > > It allows separate tables for each enumeration or you can put them > all in the same table (it uses STI in that case). > > It also has ''virtual'' enumeration classes - so if you have 20 > different enumeration types you don''t need to have 20 different files > in your models directory. > > The only thing which might make it unsuitable for you is that it > caches the values in-memory only - and I make it purposefully hard to > change the record values as a result (don''t want 3 different fcgi > listeners getting out of sync). So, if you plan on frequently > changing the values for Status records then you should look at > another solution. > > Personally, I only change these sorts of records (like status codes) > during a deploy anyhow. > > Hope this helps, > Trevor > > -- > Trevor Squires > http://somethinglearned.com > > > > On 10-May-06, at 11:56 AM, Kenneth Liu wrote: > > > Thanks everyone for your replies! They''re all very well thought out. > > > > I think I''ll go with the table and has_many approach, simply > > because it will be easier for my client to edit the descriptions. > > > > Another drawback with the pure-ruby approaches is that not storing > > the descriptions in the database will make it difficult for other > > applications (say a reporting app) to use the data, as they won''t > > have the descriptions available. (unless you want to get all > > philosophical and say that other applications shouldn''t access the > > data directly and you should use web services) > > > > Now the question is, is it ok to use the code as the primary key in > > the lookup table? Are there any gotchas to using something besides > > the automagical "id" column? > > > > Ken > > > > Wilson Bilkovich wrote: > >> On 5/10/06, Bryan Duxbury <bryan.duxbury@gmail.com> wrote: > >>> I personally like to use separate tables for lookup tables, > >>> rather than > >>> hash or class oriented approaches. The big reason: you can edit the > >>> values in the lookup table down the road, even if its only from > >>> something as simple as a generated scaffold. I think the code is a > >>> terrible place to define what possible values there should be for > >>> something like status. > >>> > >> > >> Agreed, definitely, when you need to edit the codes frequently, or > >> without restarting the app. > >> > >> I feel that full-on tables with models are overkill for simple status > >> columns, though. > >> For example, what if you only have: > >> 0 => AWAITING_REVIEW > >> 1 => ACTIVE > >> 2 => SOMETHING_ELSE > >> 3 => CLOSED > >> > >> ..or something similar. Four rows maybe wouldn''t be worth a whole > >> table, but you need a numeric code for faster indexing, etc, etc. > >> _______________________________________________ > >> Rails mailing list > >> Rails@lists.rubyonrails.org > >> http://lists.rubyonrails.org/mailman/listinfo/rails > >> > > > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060510/81cd8ea9/attachment.html
Best reason to use ids rather than some magic code is there is a posibility that the code may change somewhere down the track. e.g. if you linked order lines to an order by the order number what happens if the customer required a change to the exisiting order numbers (say adding a prefix or suffix). Youd have to change the order records and the order line records (after removing contraints and then reinstating them after the change). Using ids (something not related to the business models) you are free to change the business rules without affecting the integrity of you data. My rule of thumb is to always use integer (32/64 bit) ids to join related tables, and this id should only be used for the join (not containing some magic business model value). It''s a RDBMS construct not a business construct. If you were using an OODB then you wouldn''t have the id, the relationship would be defined through aggreagation. Ross> -----Original Message----- > From: rails-bounces@lists.rubyonrails.org > [mailto:rails-bounces@lists.rubyonrails.org]On Behalf Of Bryan Duxbury > Sent: Thursday, 11 May 2006 8:21 AM > To: rails@lists.rubyonrails.org > Subject: [Rails] Re: Re: how best to implement lookup table? > > > Why bother trying to use a different key format? It''ll "just > work" with > autonumbering ids, and you can still put an index on that field for > faster lookups if you need it. If you''re worried about having > to do two > queries to get an item and its status, use the preloading > feature that > AR provides (:include => ...). > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
That sounds good enough for me. I understand that this is the justification for using the rails "id" column in general, but thought for a moment that it didn''t apply in this case. I was wrong. Appreciate the help. Thanks. Ken Ross Dawson wrote:> Best reason to use ids rather than some magic code is there is a posibility that the code may change somewhere down the track. > > e.g. if you linked order lines to an order by the order number what happens if the customer required a change to the exisiting order numbers (say adding a prefix or suffix). Youd have to change the order records and the order line records (after removing contraints and then reinstating them after the change). Using ids (something not related to the business models) you are free to change the business rules without affecting the integrity of you data. > > My rule of thumb is to always use integer (32/64 bit) ids to join related tables, and this id should only be used for the join (not containing some magic business model value). It''s a RDBMS construct not a business construct. > > If you were using an OODB then you wouldn''t have the id, the relationship would be defined through aggreagation. > > Ross > > >> -----Original Message----- >> From: rails-bounces@lists.rubyonrails.org >> [mailto:rails-bounces@lists.rubyonrails.org]On Behalf Of Bryan Duxbury >> Sent: Thursday, 11 May 2006 8:21 AM >> To: rails@lists.rubyonrails.org >> Subject: [Rails] Re: Re: how best to implement lookup table? >> >> >> Why bother trying to use a different key format? It''ll "just >> work" with >> autonumbering ids, and you can still put an index on that field for >> faster lookups if you need it. If you''re worried about having >> to do two >> queries to get an item and its status, use the preloading >> feature that >> AR provides (:include => ...). >> >> -- >> Posted via http://www.ruby-forum.com/. >> _______________________________________________ >> Rails mailing list >> Rails@lists.rubyonrails.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >> >> >> ------------------------------------------------------------------------ >> >> _______________________________________________ >> Rails mailing list >> Rails@lists.rubyonrails.org >> http://lists.rubyonrails.org/mailman/listinfo/rails >>
On 10-May-06, at 4:49 PM, Eden Brandeis wrote:> I have looked at your plugin a couple of times, but always stayed > away because I thought it would make it difficult to change the > lookup values. But I think you are right in noting that for many > applications the lookup values won''t change that often. It would > be ideal if there was a command you could issue to lock the > application until changes to the lookups through an admin panel > were complete. This way you could schedule your update, lock the > application to prevent users from getting incorrect lookups during > the update, make the changes through your application admin views, > and then unlock the application for normal use again. > > Do you have any views on this idea? >There''s plenty of ways to do it, ranging from locking the whole user- side of the app with a "down for maintenance" message, to versioning your data so that when you''re happy with your changes you make the new version ''live'' (complicated but doesn''t involve user-perceived downtime). It all boils down to what you and your users can live with. In my experience, the only time I''ve ever done a redeployment that just contained a change to a lookup value was because of a typo. All other times I was changing logic at the same time so a redeployment was on the cards anyhow. Trev> > On 5/10/06, Trevor Squires <trevor@protocool.com> wrote: > Hey, > > I wrote a plugin for this very thing. Rather than has_many and > belongs_to you have has_enumerated and acts_as_enumerated. > > It gives you extra goodness like caching and being able to write code > that looks (imho) better: > > class Foo < ActiveRecord::Base > has_enumerated :status > end > > class Status < ActiveRecord::Base > acts_as_enumerated, :order => ''position ASC'' > end > > foo = Foo.new() > foo.status = :open > foo.status = Status[:open] > > You can get details for the plugin at http:// > www.agilewebdevelopment.com/plugins/show/4 > > It allows separate tables for each enumeration or you can put them > all in the same table (it uses STI in that case). > > It also has ''virtual'' enumeration classes - so if you have 20 > different enumeration types you don''t need to have 20 different files > in your models directory. > > The only thing which might make it unsuitable for you is that it > caches the values in-memory only - and I make it purposefully hard to > change the record values as a result (don''t want 3 different fcgi > listeners getting out of sync). So, if you plan on frequently > changing the values for Status records then you should look at > another solution. > > Personally, I only change these sorts of records (like status codes) > during a deploy anyhow. > > Hope this helps, > Trevor > > -- > Trevor Squires > http://somethinglearned.com > > > > On 10-May-06, at 11:56 AM, Kenneth Liu wrote: > > > Thanks everyone for your replies! They''re all very well thought > out. > > > > I think I''ll go with the table and has_many approach, simply > > because it will be easier for my client to edit the descriptions. > > > > Another drawback with the pure-ruby approaches is that not storing > > the descriptions in the database will make it difficult for other > > applications (say a reporting app) to use the data, as they won''t > > have the descriptions available. (unless you want to get all > > philosophical and say that other applications shouldn''t access the > > data directly and you should use web services) > > > > Now the question is, is it ok to use the code as the primary key in > > the lookup table? Are there any gotchas to using something besides > > the automagical "id" column? > > > > Ken > > > > Wilson Bilkovich wrote: > >> On 5/10/06, Bryan Duxbury <bryan.duxbury@gmail.com> wrote: > >>> I personally like to use separate tables for lookup tables, > >>> rather than > >>> hash or class oriented approaches. The big reason: you can edit > the > >>> values in the lookup table down the road, even if its only from > >>> something as simple as a generated scaffold. I think the code is a > >>> terrible place to define what possible values there should be for > >>> something like status. > >>> > >> > >> Agreed, definitely, when you need to edit the codes frequently, or > >> without restarting the app. > >> > >> I feel that full-on tables with models are overkill for simple > status > >> columns, though. > >> For example, what if you only have: > >> 0 => AWAITING_REVIEW > >> 1 => ACTIVE > >> 2 => SOMETHING_ELSE > >> 3 => CLOSED > >> > >> ..or something similar. Four rows maybe wouldn''t be worth a whole > >> table, but you need a numeric code for faster indexing, etc, etc. > >> _______________________________________________ > >> Rails mailing list > >> Rails@lists.rubyonrails.org > >> http://lists.rubyonrails.org/mailman/listinfo/rails > >> > > > > _______________________________________________ > > Rails mailing list > > Rails@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060511/f5721939/attachment.html