Rails - May 2009 - what database field type should i use ??

in a database table if there is a field which has a certain set of
fixed values. for example
staus => {Single, Married, Divorced }
OR
state => {California, Albama, Olaska ...}

so what should be preferred way out of the following for storing the
values

1. Keep the field as "string(Rails)"  VARCHAR(MySQL) itself ....and
while showing the field just show the field value.

2. Keep the field internally as a code like {:california =>
01, :albama => 02, washington => 03 ....} but while showing the state
show only the corresponding state.

By using option 2, a certain disadvantage is extra computation time
required to find out corresponding state name based on code when
showing the state field to user. But an advantage could be in terms of
smaller database. In my opinion, saving 01 as an integer could save
significant space than storing "california"  if number of records
happen to be in tens of thousands .

please suggest ??

vipin

In the case of method 2 where would you store the tens of thousands of
strings if not in the database?
Colin

2009/5/7 Vipin <sh.vipin-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
>
> in a database table if there is a field which has a certain set of
> fixed values. for example
> staus => {Single, Married, Divorced }
> OR
> state => {California, Albama, Olaska ...}
>
> so what should be preferred way out of the following for storing the
> values
>
> 1. Keep the field as "string(Rails)"  VARCHAR(MySQL) itself
....and
> while showing the field just show the field value.
>
> 2. Keep the field internally as a code like {:california =>
> 01, :albama => 02, washington => 03 ....} but while showing the state
> show only the corresponding state.
>
> By using option 2, a certain disadvantage is extra computation time
> required to find out corresponding state name based on code when
> showing the state field to user. But an advantage could be in terms of
> smaller database. In my opinion, saving 01 as an integer could save
> significant space than storing "california"  if number of records
> happen to be in tens of thousands .
>
> please suggest ??
>
> vipin
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Vipin wrote:
> in a database table if there is a field which has a certain set of
> fixed values. for example
> staus => {Single, Married, Divorced }
> OR
> state => {California, Albama, Olaska ...}
> 
> so what should be preferred way out of the following for storing the
> values
I use ENUM() columns in mysql - rails treats them as strings so 
everything works (apart from schema dumps)

just realize...

in the case od 1st method:
everything is simple, but db grows.. but... you made some typo mistake 
(eg Albama instead of Alabama) and what then.. repair entire table to 
get the correct results? weird

2nd method:
I''m using it for a small enumeration lists (not hundreds/thousands
items
- then is better to use db enumeratin methods, like countries table and 
country_id column)

in app/model/some_model.rb

@@enum_list = %w( value1 value2 value3 )

# to use in erb templates for form.select() helper
def self.enum_list_for_select
   @@enum_list.enum_with_index.collect{|k,v| [k,v]}
end

def enum_list_to_text
   self.enum_list.nil? ? ''undefined'' :
@@enum_list[self.enum_list]
end

tom

Vipin wrote:
> in a database table if there is a field which has a certain set of
> fixed values. for example
> staus => {Single, Married, Divorced }
> OR
> state => {California, Albama, Olaska ...}
> 
> so what should be preferred way out of the following for storing the
> values
> 
> 1. Keep the field as "string(Rails)"  VARCHAR(MySQL) itself
....and
> while showing the field just show the field value.
> 
> 2. Keep the field internally as a code like {:california =>
> 01, :albama => 02, washington => 03 ....} but while showing the state
> show only the corresponding state.
> 
> By using option 2, a certain disadvantage is extra computation time
> required to find out corresponding state name based on code when
> showing the state field to user. But an advantage could be in terms of
> smaller database. In my opinion, saving 01 as an integer could save
> significant space than storing "california"  if number of records
> happen to be in tens of thousands .
> 
> please suggest ??
> 
> vipin
> 
> 
-- 
==============================================================================Tomas
Meinlschmidt, MS {MCT, MCP+I, MCSE, AER}, NetApp Filer/NetCache

www.meinlschmidt.com  www.maxwellrender.cz  www.lightgems.cz
===============================================================================

2009/5/7 Andrew Porter <andy-OV3k8GMR8cdg9hUCZPvPmw@public.gmane.org>
>
> Vipin wrote:
> > in a database table if there is a field which has a certain set of
> > fixed values. for example
> > staus => {Single, Married, Divorced }
> > OR
> > state => {California, Albama, Olaska ...}
> >
> > so what should be preferred way out of the following for storing the
> > values
>
> I use ENUM() columns in mysql - rails treats them as strings so
> everything works (apart from schema dumps)
>
Sorry, I misunderstood the question, I assumed you wanted to know how to
store the country names in a countries table.  I did not realise that in
option 1 you meant storing the string in every record that references
country, though that is what you said, I did not read it carefully.  Now
that I understand, I would suggest some variant of option 2, store an id in
each record referencing a country and determine the string by an enumeration
or table lookup later.  Worrying too much about computing time during
development is a mugs game, an application always ends up with most of its
computing time in an area you do not expect.  Worry about optimisation later
when (or more likely if) a particular area becomes a problem.

Colin

>
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Sometimes it''''s a matter of taste or design constraint, but
why not
use a small join table and a foreign key? ENUM is also a great choice,
as these guys have pointed out, but sometimes there is a bit of
overhead -- but you shouldn''t worry about that until it becomes an
issue. Personally, I find it easier to maintain a simple join table
and FK relationships than to mess with ENUM field types.

I don''t know if this is still applicable, but it seems like there is a
bit of data massaging in Rails for the ENUM type (Rails converts it
internally to VARCHAR):

http://lists.rubyonrails.org/pipermail/rails/2005-January/001536.html

I like to let the database do as much work as it can, :-)

HTH!

Billee D.

On May 7, 4:43 am, Vipin
<sh.vi...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> in a database table if there is a field which has a certain set of
> fixed values. for example
> staus => {Single, Married, Divorced }
> OR
> state => {California, Albama, Olaska ...}
>
> so what should be preferred way out of the following for storing the
> values
>
> 1. Keep the field as "string(Rails)"  VARCHAR(MySQL) itself
....and
> while showing the field just show the field value.
>
> 2. Keep the field internally as a code like {:california =>
> 01, :albama => 02, washington => 03 ....} but while showing the state
> show only the corresponding state.
>
> By using option 2, a certain disadvantage is extra computation time
> required to find out corresponding state name based on code when
> showing the state field to user. But an advantage could be in terms of
> smaller database. In my opinion, saving 01 as an integer could save
> significant space than storing "california"  if number of records
> happen to be in tens of thousands .
>
> please suggest ??
>
> vipin

It''s about performance.

if you''ll have marriage status (single/married/divorced/etc) in table 
marriagestatuses, and using belongs_to/has_many via marriagestatus_id, 
and not using :include => marriagestatuses in you
''queries'', db will do
a lot of queries just for a few enumerables.

so each of us have to decide whether to use another ''enumerate
table'' or nor

tom

Billee D. wrote:
> Sometimes it''''s a matter of taste or design constraint,
but why not
> use a small join table and a foreign key? ENUM is also a great choice,
> as these guys have pointed out, but sometimes there is a bit of
> overhead -- but you shouldn''t worry about that until it becomes an
> issue. Personally, I find it easier to maintain a simple join table
> and FK relationships than to mess with ENUM field types.
> 
> I don''t know if this is still applicable, but it seems like there
is a
> bit of data massaging in Rails for the ENUM type (Rails converts it
> internally to VARCHAR):
> 
> http://lists.rubyonrails.org/pipermail/rails/2005-January/001536.html
> 
> I like to let the database do as much work as it can, :-)
> 
> HTH!
> 
> Billee D.
> 
> On May 7, 4:43 am, Vipin
<sh.vi...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>> in a database table if there is a field which has a certain set of
>> fixed values. for example
>> staus => {Single, Married, Divorced }
>> OR
>> state => {California, Albama, Olaska ...}
>>
>> so what should be preferred way out of the following for storing the
>> values
>>
>> 1. Keep the field as "string(Rails)"  VARCHAR(MySQL) itself
....and
>> while showing the field just show the field value.
>>
>> 2. Keep the field internally as a code like {:california =>
>> 01, :albama => 02, washington => 03 ....} but while showing the
state
>> show only the corresponding state.
>>
>> By using option 2, a certain disadvantage is extra computation time
>> required to find out corresponding state name based on code when
>> showing the state field to user. But an advantage could be in terms of
>> smaller database. In my opinion, saving 01 as an integer could save
>> significant space than storing "california"  if number of
records
>> happen to be in tens of thousands .
>>
>> please suggest ??
>>
>> vipin
> 
-- 
==============================================================================Tomas
Meinlschmidt, MS {MCT, MCP+I, MCSE, AER}, NetApp Filer/NetCache

www.meinlschmidt.com  www.maxwellrender.cz  www.lightgems.cz
===============================================================================

in database only ...i am going to store both string /id in database
only

On May 7, 4:45 pm, Colin Law
<clan...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org>
wrote:
> In the case of method 2 where would you store the tens of thousands of
> strings if not in the database?
> Colin
>
> 2009/5/7 Vipin <sh.vi...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
>
>
>
> > in a database table if there is a field which has a certain set of
> > fixed values. for example
> > staus => {Single, Married, Divorced }
> > OR
> > state => {California, Albama, Olaska ...}
>
> > so what should be preferred way out of the following for storing the
> > values
>
> > 1. Keep the field as "string(Rails)"  VARCHAR(MySQL) itself
....and
> > while showing the field just show the field value.
>
> > 2. Keep the field internally as a code like {:california =>
> > 01, :albama => 02, washington => 03 ....} but while showing the
state
> > show only the corresponding state.
>
> > By using option 2, a certain disadvantage is extra computation time
> > required to find out corresponding state name based on code when
> > showing the state field to user. But an advantage could be in terms of
> > smaller database. In my opinion, saving 01 as an integer could save
> > significant space than storing "california"  if number of
records
> > happen to be in tens of thousands .
>
> > please suggest ??
>
> > vipin

On May 7, 5:47 pm, Tom Z Meinlschmidt
<to...-ooGa/4BNRfSw0JuIXryQZA@public.gmane.org>
wrote:
> just realize...
>
> in the case od 1st method:
> everything is simple, but db grows.. but... you made some typo mistake
> (eg Albama instead of Alabama) and what then.. repair entire table to
> get the correct results? weird
>
> 2nd method:
> I''m using it for a small enumeration lists (not hundreds/thousands
items
> - then is better to use db enumeratin methods, like countries table and
> country_id column)
>
> in app/model/some_model.rb
>
> @@enum_list = %w( value1 value2 value3 )
>
> # to use in erb templates for form.select() helper
> def self.enum_list_for_select
>    @@enum_list.enum_with_index.collect{|k,v| [k,v]}
> end
>
> def enum_list_to_text
>    self.enum_list.nil? ? ''undefined'' :
@@enum_list[self.enum_list]
> end
>
> tom
>
>
>
> Vipin wrote:
> > in a database table if there is a field which has a certain set of
> > fixed values. for example
> > staus => {Single, Married, Divorced }
> > OR
> > state => {California, Albama, Olaska ...}
>
> > so what should be preferred way out of the following for storing the
> > values
>
> > 1. Keep the field as "string(Rails)"  VARCHAR(MySQL) itself
....and
> > while showing the field just show the field value.
>
> > 2. Keep the field internally as a code like {:california =>
> > 01, :albama => 02, washington => 03 ....} but while showing the
state
> > show only the corresponding state.
>
> > By using option 2, a certain disadvantage is extra computation time
> > required to find out corresponding state name based on code when
> > showing the state field to user. But an advantage could be in terms of
> > smaller database. In my opinion, saving 01 as an integer could save
> > significant space than storing "california"  if number of
records
> > happen to be in tens of thousands .
>
> > please suggest ??
>
> > vipin
>
> --
>
==============================================================================>
Tomas Meinlschmidt, MS {MCT, MCP+I, MCSE, AER}, NetApp Filer/NetCache
>
> www.meinlschmidt.com www.maxwellrender.cz www.lightgems.cz
>
==============================================================================
Thanks ! i will try this.

On May 7, 6:24 pm, Colin Law
<clan...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org>
wrote:
> 2009/5/7 Andrew Porter <a...-OV3k8GMR8cdg9hUCZPvPmw@public.gmane.org>
>
>
>
> > Vipin wrote:
> > > in a database table if there is a field which has a certain set
of
> > > fixed values. for example
> > > staus => {Single, Married, Divorced }
> > > OR
> > > state => {California, Albama, Olaska ...}
>
> > > so what should be preferred way out of the following for storing
the
> > > values
>
> > I use ENUM() columns in mysql - rails treats them as strings so
> > everything works (apart from schema dumps)
>
> Sorry, I misunderstood the question, I assumed you wanted to know how to
> store the country names in a countries table.  I did not realise that in
> option 1 you meant storing the string in every record that references
> country, though that is what you said, I did not read it carefully.  Now
> that I understand, I would suggest some variant of option 2, store an id in
> each record referencing a country and determine the string by an
enumeration
> or table lookup later.  Worrying too much about computing time during
> development is a mugs game, an application always ends up with most of its
> computing time in an area you do not expect.  Worry about optimisation
later
> when (or more likely if) a particular area becomes a problem.
>
> Colin
>
>
Colin,
yes i guess 2nd variant will be better.
thanks

On May 7, 8:09 pm, "Billee D."
<william.dod...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> Sometimes it''''s a matter of taste or design constraint,
but why not
> use a small join table and a foreign key? ENUM is also a great choice,
> as these guys have pointed out, but sometimes there is a bit of
> overhead -- but you shouldn''t worry about that until it becomes an
> issue. Personally, I find it easier to maintain a simple join table
> and FK relationships than to mess with ENUM field types.
>
> I don''t know if this is still applicable, but it seems like there
is a
> bit of data massaging in Rails for the ENUM type (Rails converts it
> internally to VARCHAR):
>
> http://lists.rubyonrails.org/pipermail/rails/2005-January/001536.html
>
> I like to let the database do as much work as it can, :-)
>
> HTH!
>
> Billee D.
>
> On May 7, 4:43 am, Vipin
<sh.vi...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > in a database table if there is a field which has a certain set of
> > fixed values. for example
> > staus => {Single, Married, Divorced }
> > OR
> > state => {California, Albama, Olaska ...}
>
> > so what should be preferred way out of the following for storing the
> > values
>
> > 1. Keep the field as "string(Rails)"  VARCHAR(MySQL) itself
....and
> > while showing the field just show the field value.
>
> > 2. Keep the field internally as a code like {:california =>
> > 01, :albama => 02, washington => 03 ....} but while showing the
state
> > show only the corresponding state.
>
> > By using option 2, a certain disadvantage is extra computation time
> > required to find out corresponding state name based on code when
> > showing the state field to user. But an advantage could be in terms of
> > smaller database. In my opinion, saving 01 as an integer could save
> > significant space than storing "california"  if number of
records
> > happen to be in tens of thousands .
>
> > please suggest ??
>
> > vipin
But Bilee,
if we use another table and do the mapping through foreign key won;t
it be even poorer as we will be making 2 SQL queries to access the
same record. which we are doing in one SQL query in above two
methods.

But i ll certainly check the link provided.

vipin

I would also use a small join table with your sets ... so your users
can add properties on the fly.


On May 8, 9:13 am, Vipin
<sh.vi...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On May 7, 8:09 pm, "Billee D."
<william.dod...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
>
>
> > Sometimes it''''s a matter of taste or design
constraint, but why not
> > use a small join table and a foreign key? ENUM is also a great choice,
> > as these guys have pointed out, but sometimes there is a bit of
> > overhead -- but you shouldn''t worry about that until it
becomes an
> > issue. Personally, I find it easier to maintain a simple join table
> > and FK relationships than to mess with ENUM field types.
>
> > I don''t know if this is still applicable, but it seems like
there is a
> > bit of data massaging in Rails for the ENUM type (Rails converts it
> > internally to VARCHAR):
>
> >http://lists.rubyonrails.org/pipermail/rails/2005-January/001536.html
>
> > I like to let the database do as much work as it can, :-)
>
> > HTH!
>
> > Billee D.
>
> > On May 7, 4:43 am, Vipin
<sh.vi...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > in a database table if there is a field which has a certain set
of
> > > fixed values. for example
> > > staus => {Single, Married, Divorced }
> > > OR
> > > state => {California, Albama, Olaska ...}
>
> > > so what should be preferred way out of the following for storing
the
> > > values
>
> > > 1. Keep the field as "string(Rails)"  VARCHAR(MySQL)
itself ....and
> > > while showing the field just show the field value.
>
> > > 2. Keep the field internally as a code like {:california =>
> > > 01, :albama => 02, washington => 03 ....} but while showing
the state
> > > show only the corresponding state.
>
> > > By using option 2, a certain disadvantage is extra computation
time
> > > required to find out corresponding state name based on code when
> > > showing the state field to user. But an advantage could be in
terms of
> > > smaller database. In my opinion, saving 01 as an integer could
save
> > > significant space than storing "california"  if number
of records
> > > happen to be in tens of thousands .
>
> > > please suggest ??
>
> > > vipin
>
> But Bilee,
> if we use another table and do the mapping through foreign key won;t
> it be even poorer as we will be making 2 SQL queries to access the
> same record. which we are doing in one SQL query in above two
> methods.
>
> But i ll certainly check the link provided.
>
> vipin

The question isn''t really a performance issue, but rather one of
customization. If users are likely to need to add values to an
enumeration, (example: category for a blog post), then a lookup table
is a good idea. If the values are substantially unlikely to change
(see your examples - marriage status and state) then there''s not a lot
to be gained by complicating the DB.

--Matt Jones

On May 7, 3:43 am, Vipin
<sh.vi...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> in a database table if there is a field which has a certain set of
> fixed values. for example
> staus => {Single, Married, Divorced }
> OR
> state => {California, Albama, Olaska ...}
>
> so what should be preferred way out of the following for storing the
> values
>
> 1. Keep the field as "string(Rails)"  VARCHAR(MySQL) itself
....and
> while showing the field just show the field value.
>
> 2. Keep the field internally as a code like {:california =>
> 01, :albama => 02, washington => 03 ....} but while showing the state
> show only the corresponding state.
>
> By using option 2, a certain disadvantage is extra computation time
> required to find out corresponding state name based on code when
> showing the state field to user. But an advantage could be in terms of
> smaller database. In my opinion, saving 01 as an integer could save
> significant space than storing "california"  if number of records
> happen to be in tens of thousands .
>
> please suggest ??
>
> vipin

Though you might already have solved your issue at hand, I''ve recently
ran into a similar situation and written a small plugin for it,
columns are backed by integers, but in ruby you can treat them using
symbols or whatever:

class User < ActiveRecord::Base
  as_enum :status, { :single => 0, :married => 1, :divorced => 2 }
end

Then create an integer column:

add_column :users, :status_cd, :integer

It''s then possible to easily access these values using @user.status:

@user = User.new
@user.status = :married # => implies @user.status_cd = 1

For my problem I also required some shorthands to check for values, so
I''ve added also <symbol>? methods:

@user.married? # => true
@user.single?  # => false

The code lives at github http://github.com/lwe/simple_enum/tree/master
any feedback is appreciated

cheers, lukas

On May 7, 10:43 am, Vipin
<sh.vi...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> in a database table if there is a field which has a certain set of
> fixed values. for example
> staus => {Single, Married, Divorced }
> OR
> state => {California, Albama, Olaska ...}
>
> so what should be preferred way out of the following for storing the
> values
>
> 1. Keep the field as "string(Rails)"  VARCHAR(MySQL) itself
....and
> while showing the field just show the field value.
>
> 2. Keep the field internally as a code like {:california =>
> 01, :albama => 02, washington => 03 ....} but while showing the state
> show only the corresponding state.
>
> By using option 2, a certain disadvantage is extra computation time
> required to find out corresponding state name based on code when
> showing the state field to user. But an advantage could be in terms of
> smaller database. In my opinion, saving 01 as an integer could save
> significant space than storing "california"  if number of records
> happen to be in tens of thousands .
>
> please suggest ??
>
> vipin