Database usage technique -- user-specified fields


#1

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


#2

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


#3

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


#4

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


#5

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


#6

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