# Database usage technique -- user-specified fields **URL:** \ **Category:** Rails\ **Created:** [April 4, 2006, 5:13pm UTC](https://www.ruby-forum.com/t/database-usage-technique-user-specified-fields/56825 "2006-04-04T17:13:06Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![janovetz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/53a042/32.png) [@janovetz](https://www.ruby-forum.com/u/janovetz)\ **Post date:** [April 4, 2006, 5:13pm UTC](https://www.ruby-forum.com/t/database-usage-technique-user-specified-fields/56825/1 "2006-04-04T17:13:06Z")
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 ---
**Author:** ![janovetz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/53a042/32.png) [@janovetz](https://www.ruby-forum.com/u/janovetz)\ **Post date:** [April 4, 2006, 5:39pm UTC](https://www.ruby-forum.com/t/database-usage-technique-user-specified-fields/56825/2 "2006-04-04T17:39:38Z")
> 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 ---
**Author:** ![janovetz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/53a042/32.png) [@janovetz](https://www.ruby-forum.com/u/janovetz)\ **Post date:** [April 4, 2006, 5:51pm UTC](https://www.ruby-forum.com/t/database-usage-technique-user-specified-fields/56825/3 "2006-04-04T17:51:33Z")
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 ---
**Author:** ![janovetz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/53a042/32.png) [@janovetz](https://www.ruby-forum.com/u/janovetz)\ **Post date:** [April 4, 2006, 5:55pm UTC](https://www.ruby-forum.com/t/database-usage-technique-user-specified-fields/56825/4 "2006-04-04T17:55:03Z")
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. 1. 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 ---
**Author:** ![janovetz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/53a042/32.png) [@janovetz](https://www.ruby-forum.com/u/janovetz)\ **Post date:** [April 4, 2006, 6:37pm UTC](https://www.ruby-forum.com/t/database-usage-technique-user-specified-fields/56825/5 "2006-04-04T18:37:28Z")
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 ---
**Author:** ![janovetz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/53a042/32.png) [@janovetz](https://www.ruby-forum.com/u/janovetz)\ **Post date:** [April 4, 2006, 6:30pm UTC](https://www.ruby-forum.com/t/database-usage-technique-user-specified-fields/56825/6 "2006-04-04T18:30:47Z")
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