Re: Need help with design of database (SQL)
From: Brian Inglis <Brian.dot.Inglis_at_Compuserve.com>
Date: Sat, 21 Jul 2001 23:31:09 GMT
Message-ID: <o89mit4c7vs700m99b7ioqtunvetimu4jt_at_4ax.com>
>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 |
>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.
Date: Sat, 21 Jul 2001 23:31:09 GMT
Message-ID: <o89mit4c7vs700m99b7ioqtunvetimu4jt_at_4ax.com>
On 15 Jun 2001 01:49:54 GMT, zaphod_at_services4research.de wrote:
>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.
ASource seems to depend only on AName, so the pairs should really be in a separate table, not stored redundantly in Attrubutes.
Thanks. Take care, Brian Inglis Calgary, Alberta, Canada
--
Brian.Inglis_at_CSi.com (Brian dot Inglis at SystematicSw dot ab dot ca)
fake address use address above to reply
tosspam_at_aol.com abuse_at_aol.com abuse_at_yahoo.com abuse_at_hotmail.com abuse_at_msn.com abuse_at_sprint.com abuse_at_earthlink.com abuse_at_cadvision.com abuse_at_ibsystems.com uce_at_ftc.gov
spam traps
Received on Sun Jul 22 2001 - 01:31:09 CEST