Rails - Apr 2006 - Database usage technique -- user-specified fields

Hello folks-

In one particular app, it would be useful for the customers to be able 
to specify the significance (and presence) of fields.  For example, 
consider a CONTACTs database.  User 1 wants to have phone1, phone2, 
phone3, and User 2 wants to have 4 address fields.

Generically, this could be done by having a CONTACT with, say, 10 
strings, 10 integers, etc.  The user''s account would specify the 
significance of each field.

What isn''t clear to me is the toll this would take on a database.  Say 
my CONTACT looks like:

CONTACT:
  first_name, :string
  last_name, :string
  string1, :string
  string2, :string
    ...
  string20, :string
  integer1, :integer
  integer2, :integer
    ...
  integer20, :integer

If there are hundreds of users each with hundreds of CONTACTs, these 
extra fields lying around will take up space.  Granted, unused ones will 
be NULL, but still taking up space.

Another option is to place the "user-specified" fields in a separate 
table, but this seems less desirable from a search/sort point of view.

I''d appreciate any comments on this!

   Thanks,
   Jake

-- 
Posted via http://www.ruby-forum.com/.

> 
> If there are hundreds of users each with hundreds of CONTACTs, these 
> extra fields lying around will take up space.  Granted, unused ones will 
> be NULL, but still taking up space.
> 
> Another option is to place the "user-specified" fields in a
separate
> table, but this seems less desirable from a search/sort point of view.
> 
> I''d appreciate any comments on this!
> 
>    Thanks,
>    Jake
Jake,

I''d suggest putting the data in several other tables, one for each 
class.  If you don''t, your code is going to be a mess.  Maybe something
like:

CONTACT:
  first_name, :string
  last_name, :string

ADDRESS:
  type, :string ## e.g. HOME, WORK, etc
  street1, :string
  street2, :string
  city, :string
  ..
  ..

PHONE:
  type, :string ## e.g. HOME, WORK, etc
  phone_number, :string
  ..
  ..

Dan



-- 
Posted via http://www.ruby-forum.com/.

Seems unnecessarily complex to me.

I achieved the same effect by doing this.

table :contacts
  id :integer
  user_id :integer
  kind :string
  value :string
  position :integer
end

Kind can be ''phone number, fax number, email, skype name,
etc...''

class Contacts < AR:Base
  belongs_to :user
  acts_as_list :scope=>:user
end

Then just set up a view that adds or deletes ''contacts'' for
the user.
The priority can be adjusted using the acts_as_list API to modify the  
position.

This doesn''t waste any space and there is no arbitrary limit to the  
number of contacts a user can have.

_Kevin


On Tuesday, April 04, 2006, at 5:13 PM, Jake Janovetz
wrote:
>Hello folks-
>
>In one particular app, it would be useful for the customers to be able
>to specify the significance (and presence) of fields.  For example,
>consider a CONTACTs database.  User 1 wants to have phone1, phone2,
>phone3, and User 2 wants to have 4 address fields.
>
>Generically, this could be done by having a CONTACT with, say, 10
>strings, 10 integers, etc.  The user''s account would specify the
>significance of each field.
>
>What isn''t clear to me is the toll this would take on a database. 
Say
>my CONTACT looks like:
>
>CONTACT:
>  first_name, :string
>  last_name, :string
>  string1, :string
>  string2, :string
>    ...
>  string20, :string
>  integer1, :integer
>  integer2, :integer
>    ...
>  integer20, :integer
>
>If there are hundreds of users each with hundreds of CONTACTs, these
>extra fields lying around will take up space.  Granted, unused ones will
>be NULL, but still taking up space.
>
>Another option is to place the "user-specified" fields in a
separate
>table, but this seems less desirable from a search/sort point of view.
>
>I''d appreciate any comments on this!
>
>   Thanks,
>   Jake
>
>--
>Posted via http://www.ruby-forum.com/.
>_______________________________________________
>Rails mailing list
>Rails@lists.rubyonrails.org
>http://lists.rubyonrails.org/mailman/listinfo/rails



-- 
Posted with http://DevLists.com.  Sign up and save your mailbox.

Dan Perez wrote:
> Jake,
> 
> I''d suggest putting the data in several other tables, one for each
> class.  If you don''t, your code is going to be a mess.  Maybe
something
> like:
> 
> CONTACT:
>   first_name, :string
>   last_name, :string
> 
> ADDRESS:
>   type, :string ## e.g. HOME, WORK, etc
>   street1, :string
>   street2, :string
>   city, :string
>   ..
>   ..
> 
> PHONE:
>   type, :string ## e.g. HOME, WORK, etc
>   phone_number, :string
>   ..
>   ..
> 
> Dan

Well, the problem is that these column names won''t be known in advance.
They are user-defined.  Consider a system like Basecamp where each 
contact can have several additional fields (like "likes_chocolate"). 
The site administrator determines which of these fields is available and 
what their datatype is (bool, string, integer, float).

The options seem to be:
1. Put a bunch of extra fields in the table that contains the contacts. 
Then have something else determine how those fields are applied.

--> This solution is nice in that all the data is in the row where the 
contact is.  But is potentially wastes space since not all these extra 
fields will be used by all sites.

2. Have another table store keys associated with each site.  Then 
another table store key/value pairs associated with each contact.

--> This solution seems more elegant, but I''m not sure how it
affects
search and sort behavior.

   Jake

-- 
Posted via http://www.ruby-forum.com/.

Ahh.  I didn''t quite understand the whole problem.  Kevin''s
right,
obviously you don''t want to create a new class for each object if you 
don''t even know the name of these ''fields'' that the
users might create.

imho, go with something along the lines of option 2.

Dan


-- 
Posted via http://www.ruby-forum.com/.

On Tue, Apr 04, 2006 at 05:39:38PM +0200, Dan Perez wrote:
} > 
} > If there are hundreds of users each with hundreds of CONTACTs, these 
} > extra fields lying around will take up space.  Granted, unused ones will 
} > be NULL, but still taking up space.
} > 
} > Another option is to place the "user-specified" fields in a
separate
} > table, but this seems less desirable from a search/sort point of view.
} > 
} > I''d appreciate any comments on this!
} > 
} >    Thanks,
} >    Jake
} 
} Jake,
} 
} I''d suggest putting the data in several other tables, one for each 
} class.  If you don''t, your code is going to be a mess.  Maybe
something
} like:
} 
} CONTACT:
}   first_name, :string
}   last_name, :string
} 
} ADDRESS:
}   type, :string ## e.g. HOME, WORK, etc
}   street1, :string
}   street2, :string
}   city, :string
}   ..
}   ..
} 
} PHONE:
}   type, :string ## e.g. HOME, WORK, etc
}   phone_number, :string
}   ..
}   ..

I''ve been thinking of creating an acts_as_struct or acts_as_hash plugin
for
this sort of purpose. Does this interest anyone?

} Dan
--Greg