Need help for Dynamic tables

5 pts.
Tags:
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..
ASKED: June 6, 2007  9:45 AM
UPDATED: June 6, 2007  11:15 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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)

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following