Forum: Ruby on Rails Database usage technique -- user-specified fields

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Jake J. (Guest)
on 2006-04-04 19:13
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
Dan P. (Guest)
on 2006-04-04 19:39
>
> 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
Kevin O. (Guest)
on 2006-04-04 19:51
(Received via mailing list)
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
Jake J. (Guest)
on 2006-04-04 19:55
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
Dan P. (Guest)
on 2006-04-04 20:30
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
Gregory S. (Guest)
on 2006-04-04 20:37
(Received via mailing list)
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
This topic is locked and can not be replied to.