Need help with design of database (SQL)

From: <zaphod_at_services4research.de>
Date: Sat, 21 Jul 2001 23:31:00 GMT
Message-ID: <9gbpk2$dmn$1_at_news.online.de>

Hi!

I need some help for the design of a database.

Lets think of a database with two tables:

1. Table: Person

PID: Persons ID
PName: Name of Person

---

| Table Person |

 +-------------------------------------+


|PID | PName                          |


 +----+--------------------------------+


|  1 | Paul                           |

|  2 | Peter                          |


|  3 | Mary                           |


+-------------------------------------+

2. Table: Attributes

PID: Persons ID (as in table Person)
ASource: source of attributes
AName: name of an attribute
AValue: value for attribute

---

| Table Attributes |

 +-------------------------------------+


|PID | ASource | AName   | AValue     |


 +----+---------+-------+--------------+


|  1 | INFO1   | sex     | male       |

|  1 | INFO1   | age     | 42         |

|  1 | INFO2   | hobbies | golf       |

|  1 | INFO2   | hobbies | cars       |

|  2 | INFO1   | sex     | male       |

|  2 | INFO1   | age     | 33         |

|  3 | INFO1   | sex     | female     |

|  3 | INFO1   | age     | 82         |

|  3 | INFO2   | hobbies | paragliding|

|  3 | INFO2   | hobbies | cars       |


|  3 | INFO2   | pets    | dog        |


+-------------------------------------+

This is the design so far. Normally the attributes in the table Attributes would be fields in the table Person. I decided to have them in a seperate table because I often have multiple values for an attribute. Second: with the time the number of attributes will grow when new attributesources with 50 or 100 attributes have to be stored into the database. This will be often enough.

Actually I am quite happy with the design so far, but I have one big problem:

Most of the time I want to query the database like this:

Example:

Give me all persons (PIDs) who are INFO1->sex=male and whose INFO2->hobbies are golf and cars.

Example:
Give me all persons (PIDs) who (INFO1->age>=20 and INFO1->age<=40) and (INFO2-hobbies=cars or INFO2->hobbies=golf or INFO2->pets=cats)

The condition could contain as many attributes as necessary to define the desired group of people.

So, if the database design I chose is ok, how do I write the SQL-Queries? If not: How should I design such a database?

I would be glad for any helpful comments.

Regards
Thomas Received on Sun Jul 22 2001 - 01:31:00 CEST