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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
