This is more of a design issue I''m wrestling with, and was wondering what other people are doing. I have a products table with status values of In Stock, Out of Stork or Discontinued. This is unlikely to change any time soon. Approach A, is to have thses actual values in the status column, at least the meaning is very apparent, yet querying for products with a specific status could be problematic if due to a business decision the description had to be changed, e.g. Out of Stock became Not in Stock. Approach B, is to have a code that represents the status, such as I, O, D, querying would work, and their descriptions could change. But when the data was displayed the code would have to be translated to a description every time. Approach C, have a new status look up table, which would have an id column and a description, and the products table would then have a status id, which if the description was required would include the status table in the find. This seems a bit over the top but maybe is the cleanest way and status can be added and descriptions changed without any changes to application code. Approach D, have a new table with status codes I, O, D but that would mean hard coding a lot of the queries as find_by_sql, to have the table join, as there is no id column on the status table. Andrew -- Posted via ruby-forum.com. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Mark Reginald James
2006-Nov-08 14:08 UTC
Re: How to implement status codes in DB and Rails
Andrew Cleland wrote:> Approach C, have a new status look up table, which would have an id > column and a description, and the products table would then have a > status id, which if the description was required would include the > status table in the find. This seems a bit over the top but maybe is the > cleanest way and status can be added and descriptions changed without > any changes to application code.This works well in conjunction with Trevor Squires'' acts_as_enumerated plugin. Using this, statuses are stored in the DB as ids, tested in code using symbolic name fields, and displayed in views as either the name field or a separate description field: svn.protocool.com/rails/plugins/enumerations_mixin/trunk -- We develop, watch us RoR, in numbers too big to ignore. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On Nov 8, 2006, at 3:46 AM, Andrew Cleland wrote:> > This is more of a design issue I''m wrestling with, and was wondering > what other people are doing. > > I have a products table with status values of In Stock, Out of > Stork or > Discontinued. This is unlikely to change any time soon. > > Approach A, is to have thses actual values in the status column, at > least the meaning is very apparent, yet querying for products with a > specific status could be problematic if due to a business decision the > description had to be changed, e.g. Out of Stock became Not in Stock. > > Approach B, is to have a code that represents the status, such as > I, O, > D, querying would work, and their descriptions could change. But when > the data was displayed the code would have to be translated to a > description every time. > > Approach C, have a new status look up table, which would have an id > column and a description, and the products table would then have a > status id, which if the description was required would include the > status table in the find. This seems a bit over the top but maybe > is the > cleanest way and status can be added and descriptions changed without > any changes to application code. > > Approach D, have a new table with status codes I, O, D but that would > mean hard coding a lot of the queries as find_by_sql, to have the > table > join, as there is no id column on the status table. > > AndrewYou can use a state pattern for this and it works out pretty clean. There is also the acts_as_state_machine plugin you may want to check out. But here is a simple example of the state pattern used in credit card settlements. # id :integer(11) not null, primary key # name :string(80) default(), not null # description :text class SettlementState < ActiveRecord::Base has_many :settlements, :order => ''settlements.id'' end class Settlement < ActiveRecord::Base belongs_to :settlement_state belongs_to :ledger validates_presence_of :xaction validates_presence_of :message def authorized self.settlement_state = SettlementState.find_by_name(''authorized'') end def captured self.settlement_state = SettlementState.find_by_name(''captured'') end def voided self.settlement_state = SettlementState.find_by_name(''voided'') end def credited self.settlement_state = SettlementState.find_by_name(''credited'') end def errored self.settlement_state = SettlementState.find_by_name(''errored'') end def settled self.settlement_state = SettlementState.find_by_name(''settled'') end def is_authorized? self.settlement_state.name == ''authorized'' end def is_captured? self.settlement_state.name == ''captured'' end def is_voided? self.settlement_state.name == ''voided'' end def is_credited? self.settlement_state.name == ''credited'' end def is_errored? self.settlement_state.name == ''errored'' end def is_settled? self.settlement_state.name == ''settled'' end end Cheers- -- Ezra Zygmuntowicz -- Lead Rails Evangelist -- ez-NLltGlunAUd/unjJdyJNww@public.gmane.org -- Engine Yard, Serious Rails Hosting -- (866) 518-YARD (9273) --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Joe Ruby MUDCRAP-CE
2006-Nov-08 20:23 UTC
Re: How to implement status codes in DB and Rails
Andrew Cleland wrote:> Approach C, have a new status look up table, which would have an id > column and a description, and the products table would then have a > status id, which if the description was required would include the > status table in the find. This seems a bit over the top but maybe is the > cleanest way and status can be added and descriptions changed without > any changes to application code.Approach C is the only way that conforms to 3NF. Joe -- Posted via ruby-forum.com. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---