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: Tricky database definition problem Message-ID: Date: 10 Jan 90 18:50:28 GMT Sender: davidm@cimshop.UUCP Organization: Consilium Inc., Mountain View, California. Lines: 31 I've been having a problem designing a few databases on a couple of projects that have an inherent problem that I can't seem to normalize: 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). My inclination would be to model this in a relational database as two tables (one for entities and one for properties). The two tables would relate to one another via the EntityID (in this case, X or Y). 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 that turns out to be "not too tough", then how about the request "Find the entities having all of these properties (...) or all of these properties (...)?" Finally, how about the request "Find the entities having any of these properties (...) and any of these properties (...)"? Inquiring minds want to know... ;-) -- =================================================================== 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!"