Design question: Storing user fields


#1

Hello-

I have a database/rails design question. I’d like the user to be able
to specify a set of fields they would like associated with items.

Imagine a shopping cart where each item has a description and price.
But a particular customer may choose to associated a color with all her
items. (it will be site-wide, so every item listed for this particular
user will have this additional field).

I have considered it two ways:

  1. Stored in a pair of tables (keys and fields):
    keys: id, user_id, name, type
    fields: id, user_id, key_id, item_id, value
    (type could be “string” or “number” or something to tell the view how
    to render the input box.

  2. Stored in a serialized hash in the item:
    item: id, user_id, description, price, add_on_fields

Can someone suggest the merits of each approach? I could make either
work, I think, but I don’t have a good feel for how performance is
affected in each case. They seem similarly complex.

Jake


#2

Am Dienstag, den 31.01.2006, 06:22 +0100 schrieb Jake J.:

I have considered it two ways:
Can someone suggest the merits of each approach? I could make either
work, I think, but I don’t have a good feel for how performance is
affected in each case. They seem similarly complex.

Jake

I would prefer the second version. It is much more easier to use, but
lacks of all benefits you gain from having individual columns for each
attribute like validations, sorting or querying. So if you want to do
something like this with your attributes you should take the first
approach, wich may even result in a better performance then.

Norman T.

http://blog.inlet-media.de


#3

Norman T. wrote:

Am Dienstag, den 31.01.2006, 06:22 +0100 schrieb Jake J.:

I have considered it two ways:
Can someone suggest the merits of each approach? I could make either
work, I think, but I don’t have a good feel for how performance is
affected in each case. They seem similarly complex.

I would prefer the second version. It is much more easier to use, but
lacks of all benefits you gain from having individual columns for each
attribute like validations, sorting or querying. So if you want to do
something like this with your attributes you should take the first
approach, wich may even result in a better performance then.

Thanks Norman. I think the database table method seems a bit more
powerful with regards to search and so on. It will be a fun experiment
regardless. :slight_smile:

Jake