Re: How to enter lists into database:Example.

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(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco