Path: utzoo!utgpu!jarvis.csri.toronto.edu!cs.utexas.edu!uunet!cimshop!davidm From: cimshop!davidm@uunet.UU.NET (David S. Masterson) Newsgroups: comp.databases Subject: Re: Tricky database definition problem Message-ID: Date: 11 Jan 90 20:52:38 GMT References: <49@arkham.enet.dec.com> Sender: davidm@cimshop.UUCP Organization: Consilium Inc., Mountain View, California. Lines: 25 In-reply-to: may@28182.dec.com's message of 10 Jan 90 19:13:38 GMT In article <49@arkham.enet.dec.com> may@28182.dec.com (Patrick May) writes: What you need is an associative table between an entity table and a property table. Rows in the associative table would be composed of the primary key from the entity table and the primary key of one of the associated (hence the name) property table rows. This approach allows you to store non-primary key information about the properties once and once only. I'm beginning to see that I left out a key ingredient to my question. Seeing the replies has shown me that it is a key ingredient -- I didn't realize it before. What I was trying to do was to arrange ("normalize"?) my database in order to answer all possible queries in a static fashion. That is, I didn't want to use dynamic SQL to answer a query like "where property in or property in and ..." which I beginning to see is not possible (the query gets even worse when property becomes multivalued). I don't suppose there is an SQL construct like "where is in <,>" (the two where clauses are not really related)? Perhaps such a construct would have value? -- =================================================================== David Masterson Consilium, Inc. uunet!cimshop!davidm Mt. View, CA 94043 =================================================================== "If someone thinks they know what I said, then I didn't say it!"