Skip site navigation (1) Skip section navigation (2)

Re: weird structure

From: Renato De Giovanni <rdg(at)viafractal(dot)com(dot)br>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: weird structure
Date: 2000-08-24 22:15:35
Message-ID: 39A59E87.B882AB22@viafractal.com.br (view raw or flat)
Thread:
Lists: pgsql-sql
Yes, Ryan, the idea is to use only one row with all attributes in it.

The structure I described is easy to use when you want to know the attributes
of a single person, and in this case your suggestion is the way to go - I knew
that.

I asked the question considering a specific person_id just to simplify. My real
concern is about retrieving data from more than one person in a query.
If attributes were all columns on "person" table, than a "select
specific_fields from person where conditions=..." would do the job perfectly.
But how could I achieve the same result using that different database schema?

> Does it have to be in one row?
>
> Otherwise, assuming that person_data.d_person_id references person.a_id and
> person_data.d_attribute_id references person_attribute.a_id:
>
> select a.a_name from person p, person_data d, person_attribute a where
> p.p_name = 'UserYou'reLookingFor' AND p.p_id = d.d_person_id AND
> d.d_attribute_id = a.a_id
>
> Would return a list of attributes the person has, one per row.
>
> ----- Original Message -----
> > Consider the following tables/fields:
> > table "person": fields "p_id", "p_name".
> > table "person_attribute": fields "a_id", "a_name".
> > table "person_data": fields "d_person_id", "d_attribute_id",
> > "d_value".
> >
> > Also consider that a person may not have data related to all possible
> > attributes.
> >
> > Using this structure, how could I retrieve in one row with a single
> > select statement all attributes from a person (showing null to
> > attributes that were not registered to him)?
> >
> > Thanks for any suggestion,
> > --
> > Renato
> > Sao Paulo - SP - Brasil
> > rdg(at)viafractal(dot)com(dot)br



In response to

pgsql-sql by date

Next:From: Tatsuo IshiiDate: 2000-08-25 01:07:27
Subject: Re: sorting in UNICODE table
Previous:From: Ryan WilliamsDate: 2000-08-24 20:51:08
Subject: Re: weird structure

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group