Re: [OT] "advanced" database design (long) - Mailing list pgsql-general

Hello
________________________________

        De : pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] De la part de Alex Turner
        Envoyé : lundi 4 février 2008 05:14
        À : Lewis Cunningham
        Cc : vladimir konrad; pgsql-general@postgresql.org
        Objet : Re: [GENERAL] [OT] "advanced" database design (long)
       
       
        I"m not a database expert, but wouldn't
       
        create table attribute (
          attribute_id int
          attribute text
        )
       
        create table value (
          value_id int
          value text
        )
       
        create table attribute_value (
          entity_id int
          attribute_id int
          value_id int
        )
       
        give you a lot less  pages to load than building a table with say 90 columns in it that are all null, which would result in better rather than worse performance?

       
        Alex
       
       
        On Feb 2, 2008 9:15 AM, Lewis Cunningham <lewisc@rocketmail.com> wrote:
       

                --- vladimir konrad <vk@dsl.pipex.com> wrote:
               
                > I think that I understand basic relational theory but then I had an
                > idea.
               
                > Basically, instead of adding field to a table every time there is a
                > need for it, have a table split in two: one holds identity (id) and
                > one holds the attributes (linked to this id).
               
                > Basically, if in the future user decides that the subject should
                > have a new attribute, he can simply add "attribute definition" and
                > attribute_definition_set (if any) and the application would handle
               
               
                Basically, you would be creating your own data dictionary (i.e.
                system catalog) on top of the db data dictionary.  The database
                already comes with a way to easily add columns: ddl.  I have seen
                newbie database designers reinvent this method a hundred times.  The
                performance hits and complexity of querying data would far out weigh
                any perceived maintenance gain.

               
This model is known as Entity-Value-Attribute and not well appreciated by relational designers. I think it is not relational, but I use it as storage for data (in some case, I don't know the database structure -relational- where data will be stored). It's like a truck container used for office removal :).

Imho, don't use it with a complex database structure; as a minimum, use lookup tables for Value (to avoid uncontrolled new parameters) and add a column to store the attribute type.

Jacques Massé