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..
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!
Discuss This Question: