Re: How to enter lists into database:Example. - Mailing list pgsql-sql

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