Re: Custom Fields Database Architecture

Custom fields are a fact of life, and used in many, many business
critical applications. EAV sucks, as you mentioned, but that doesn't
take away from the requirement to build that kind of system.

From the user's perspective: If you design an application for me and I
want to add a new data field or a new form, should I have to call you
back and pay your exorbitant consulting fees? I would prefer to pay a
little bit more at the beginning and be able to add what I want into the
framework that was already built.

We handled this at one client by actually generating the ddl statements
and actually building the table/fields, including relationships (user
chooses a related object from a list and that is generated as a foreign
key). This was after we threw out their EAV system, which sucked. This
can lead to design inefficiencies and not-normalized structure, will
will lead to reporting havoc, but it depends on the requirements of the
user.

Gnanam's problem is exasperated by having multiple customers adding
multiple fields that only they can see.

I don't know your situation, so this might be off-base for your needs,
but I would try a similar approach to what I suggested above. Have base
fields in one table, with a customerid, indicating who can see the row,
and then create a custom table per client who wants to add fields. The
tablename can start with their customerid and can have security rights
automatically assigned to it.

Problems with this approach that I have seen is when the user adds 10
numeric fields, that should be normalized and then wants to generate an
aggregate query from all of them.

For most data gathering, this should be fine.

Sim

David Fetter wrote:
> On Mon, Jun 15, 2009 at 06:04:25AM -0700, Gnanam wrote:
>> Hi,
>>
>> I'm designing a database schema in which I should allow user to create
>> custom fields at the application level.
>
> This is called EAV (Entity-Attribute-Value), and it's a
> multi-decade-old mistake. Re-think your design.
>
> http://archives.postgresql.org/pgsql-general/2008-02/msg00075.php
> http://decipherinfosys.wordpress.com/2007/01/29/name-value-pair-design/
> http://en.wikipedia.org/wiki/Inner-Platform_Effect
>
> Cheers,
> David.