storing values in different tables based on data type
Posted by: Rutger Buijzen
Date: March 13, 2006 10:39AM

LS,

Suppose I need to store multiple properties of multiple data types. Wat would be the best way to store the property values?

Like:

PROPERTIES(ID,NAME,VALUE_TYPE)

PROPERTY_VALUES_NUMERIC(ID,NUMERIC_VALUE,PROPERTY_ID)
PROPERTY_VALUES_STRING(ID,STRING_VALUE,PROPERTY_ID)
PROPERTY_VALUES_DATE(ID,DATE_VALUE,PROPERTY_ID)
...

Or:

PROPERTY_VALUES(ID,NUMERIC_VALUE,TRING_VALUE,DATE_VALUE,...,PROPERTY_ID)

Suppose I choose the first option and I needed to construct a SQL query to display all properties and their respective value, what would the SQL look like? Is it possible to (LEFT) JOIN the PROPERTIES records to their value in the respective PROPERTY_VALUES_... table based on the VALUE_TYPE stored in the PROPERTIES table? Something like:

IF VALUE_TYPE == NUMERIC > LEFT JOIN PROPERTY_VALUES_NUMERIC ON ...
IF VALUE_TYPE == STRING> LEFT JOIN PROPERTY_VALUES_STRING ON ...
IF VALUE_TYPE == DATE> LEFT JOIN PROPERTY_VALUES_DATE ON ...

Thanks in advance

Options: ReplyQuote


Subject
Written By
Posted
storing values in different tables based on data type
March 13, 2006 10:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.