Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!purdue!decwrl!shlump.nac.dec.com!arkham.enet.dec.com!nmeser!may From: may@28182.dec.com (Patrick May) Newsgroups: comp.databases Subject: Re: Tricky database definition problem Message-ID: <49@arkham.enet.dec.com> Date: 10 Jan 90 19:13:38 GMT Sender: news@arkham.enet.dec.com Organization: Digital Equipment Corporation Lines: 22 In article , cimshop!davidm@uunet.UU.NET (David S. Masterson) writes... >Assume that you have some entities that each have a list of properties related >to it. For instance, a given entity X might have properties (a, b, c) whereas >another entity Y might have properties (a,b, x, y, z). There is nothing in >the definition of properties that separates them into different types of >entities/attributes -- they are all just "properties". Note also that each >entity can have a different list of properties both in number and value (X has >3, Y has 5). Finally, do not assume that either the entity identifier or the >property values are representable by one attribute (in the basic problem >definition -- I don't know about contrived attributes). 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. >Given this, how would someone write an SQL query to answer a request like >"Find the entities having all of the following list of properties (...)"? If Queries of this sort are then trivial accesses of the associative table.