Re: User Defined Fields - HELP PLEASE!

news.hp.com wrote:
> I've got a political (and technical question) I could use a hand with.
>
> I developed (and maintain) an inventory system for a large company. It has
> over 150 regular users, one of which is bucking really hard for 5-10 "User
> Defined Fields". Read that as free-form text fields.
>
> Can anyone here present a strong argument as to why doing this as a bad
> idea? Management, as usual, doesn't understand that allowing users to put
> any random data they want in these fields is bad in the long run.
>
> It'd be nice if you can point me to reference material where this is spoken
> against (either online or in currently available publications). I need
> something concrete to take with me to a meeting this afternoon.
>
> Thanks in advance!
>
>

If there is data which is relevant, and is too rich to be stored in your database "as is", then I am afraid that I would have to support the irritating user. You should come clean and say that this is not supported, but that you are happy to re-design the database should this requirement be sufficiently important.

I am sure you understand the best ways to model data, and it is VERY unlikely that this would be user defined fields.

I normally solve similar problems with the introduction with an attribute-value table (and some supporting tables) like this:

product_information(product_id int, attribute_id int, value varchar)

for each product you can then store any number of attribute-value pairs which will hold the extra information. You don't even need to touch your original table.

Incidentally, the reason that tacking 5-10 "fields" onto the "side" of a large table holding your products is a bad idea is normalisation. With N extra "fields" and L lines, you would start with NL null values. Google for normalisation to find out more.

John Received on Wed Feb 16 2005 - 16:48:34 CET