pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Frederick Klauschen <fklauschen@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to enter lists into database:Example.
Date: Mon, 24 Sep 2001 09:10:27 -0700
Message-ID: <web-124461@davinci.ethosmedia.com> (raw)
In-Reply-To: <20010924153919.31897.qmail@web10105.mail.yahoo.com>

Frederick,

> The problem is that I do not know what kind of
> attributes the user wants to enter. So I do not
> want to create a table with attributes like
> "Hometown", "eye color" , etc. The user should just
> enter what he likes to.

This is actually a common SQL issue.  If I knew a good SQL book in
German (your 1st language, correct?) I would simply recommend it.

As it is:

While arrays may seem an attractive and simple answer to the issue of
"how do I store an undefined list of characteristics" it is not the
relationally correct answer, and will lead to database normalization
proplems later on.  Instead, you need to use what I call a "vertical
characteristic table".

As in your example

Main Table: People
CREATE TABLE PEOPLE (
	people_id SERIAL
	name VARCHAR (60)
	address VARCHAR (200)
);

data:
20	Mary Stuart 	1600 Pensylvannia Ave.

Vertical Table: people_attributes
CREATE TABLE people_attributes (
	attribute_id SERIAL
	people_id INT NOT NULL REFERENCES people(people_id)
	attribute_name VARCHAR (40)
	attribute_value VARCHAR (100)
);

data:
47	20	"Hair"		"Brown"
48	20	"Eyes"		"Hazel"
49	20	"Hobby"		"Breeding pedigree mice."

Now, a crucial part of this scheme is defining an available list of
attribute types.  While not all "people" have to have all "attributes",
without a pre-defined list searches will become impossible.

PART II: Searching the attributes

First, build two tables whose structure mirrors exactly "people" and
"people-attributes" above: "searches" and "search_attributes".

Second, Construct an interface so that a user can populate the
search_attributes for any individual search, thus:

"searches"
31	"mark"		"mark's first search"

"search_attributes"
22	31	"hair"		"brown"
23	31	"hobby"		"mice"

Then use the following query:

SELECT people.people_id, people.name, people.address,
	people_attributes.attribute_name, 		
        people_attributes.attribute_value
FROM people, people_attributes
WHERE people.people_id = people_attributes.people_id
  AND people.people_id IN 
      ( SELECT people_id FROM people_attributes, search_attributes 
        WHERE search_id = 31
        AND people_attributes.attribute_name = 
            search_attributes.attribute_name
        AND people_attributes.attribute_value ~* 
            search_attributes.attribute_value )
(the ~* allows searches on partial value matches)

This will give you these results:

20	Mary Stuart	1600 Pensylvannia Ave.	Hair	Brown
20	Mary Stuart	1600 Pensylvannia Ave.	Eyes	Hazel
20	Mary Stuart	1600 Pensylvannia Ave.	Hobby	Breeding pedigree mice.

It's up to you to format them in a pleasing way.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco







Message-ID: <web-124461@davinci.ethosmedia.com>
Permalink:  ../web-124461@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-124461@davinci.ethosmedia.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: josh@agliodbs.com, fklauschen@yahoo.com
  Subject: Re: How to enter lists into database:Example.
  In-Reply-To: <web-124461@davinci.ethosmedia.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox