@David: You wrote in the links cited "The "flexibility" stems from
fear of making a design decision.". That's an important note.
Nevertheless, there are use cases where you *can not* know in advance
what the name is of the attribute! To me that's not fear but
adaptiveness, modesty and knowing when to break the rules! An
apparent successful example of this EAV design is OpenStreetMap
(http://wiki.openstreetmap.org/wiki/Database_schema).
@Gnanam: Look also at hstore which is an indexable(!) PostgreSQL data
type for storing sets of (key,value) pairs within a single data field:
http://www.postgresql.org/docs/8.3/static/hstore.html
-S.
2009/6/15 David Fetter <david@fetter.org>:
> 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.
> --
> David Fetter <david@fetter.org> http://fetter.org/
> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
> Skype: davidfetter XMPP: david.fetter@gmail.com
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>