Question

Need help for Dynamic tables

Data analysis

In a normal table all the field are defined before.. For example; let's say that we have a Cars Table, it probably will be like this:

TABLE_CARS

CAR_CODE
BRAND
MODEL
VERSION
SPEED
COLOR
GEAR_TYPE
PRICE


In such a table a single row contains all the data for a single car, so by querying any field of table we can find the car that we look for..

But in the structure I want to implement, the fields of the car is defined dynamically as properties.. In such a structure there are three tables:

TABLE_PROPERTIES
----------------------
PROPERTY_CODE
PROPERTY_NAME



TABLE_PROPERTY_ANSWERS
----------------------
PROPERTY_CODE
ANSWER_CODE
ANSWER_TEXT


TABLE_CAR_DETAILS
----------------------
CAR_CODE
PROPERTY_CODE
ANSWER_CODE

In this structure, a field named as COLOR in a regular table, changes into a property name in TABLE_PROPERTIES table..

When we try to find a car of X COLOR (AND) Y GEAR_TYPE we cant write a single where clause, because every property of the car in TABLE_CAR_DETAILS coressponds to different row.. Althoug the two row seperately satisfies our search, because of the fact that they are at different row our query returns false..

To get rid of that I use sub queries like IN (Select... or EXISTS (Select..

But since the property number is n and answer number is n, when the value for n increaes the query gets bottlenecked..

This consumes to much resourcess..

I'm looking for a new technique to solve this problem, I hope you can help me..

Regards..

Generally, you want to avoid sub-queries. You can find an item with two properties as follows:

SELECT data
___FROM mainRecord m,
______propertyRecord p1,
______propertyRecord p2
___WHERE m.mToPKey=p1.mToPKey AND
______p1.property='Color' AND
______p1.value='Red' AND
______m.mToPKey=p2.mToPKey AND
______p2.property='Gear' AND
______p2.value='Manual'

---
Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)