Re: Need help with design of database (SQL)
Date: Sat, 21 Jul 2001 23:31:02 GMT
Message-ID: <XpeW6.882$vF.152658_at_news.pacbell.net>
If your database engine supports INTERSECT operation, then something simple
like this will work:
turn each AND into INTERCECT, each OR into UNION ( and put parenthesis
appropriately)
(example1)
SELECT PID
FROM Attribute
WHERE ASource = 'INFO1'
AND AName = 'sex'
AND AValue = 'male'
INTERSECT
SELECT PID
FROM Attribute
WHERE ASource = 'INFO2'
AND AName = 'hobbies'
AND AValue = 'golf'
INTERSECT
SELECT PID
FROM Attribute
WHERE ASource = 'INFO2'
AND AName = 'hobbies'
AND AValue = 'cars'
ORs for the same attribute name can also be combined like this: (example2)
SELECT PID
FROM Attribute
WHERE ASource = 'INFO1'
AND AName = 'age'
AND CAST(int,AValue) BETWEEN 20 AND 40
INTERCEPT
SELECT PID
FROM Attribute
WHERE ASource = 'INFO2'
AND AName = 'hobbies'
AND AValue IN ( 'cars','golf','pets)
Isaac
<zaphod_at_services4research.de> wrote in message
news: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:02 CEST