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

neither method looks at all attractive

i think you are trying to abstract a design?

why not model the real data?

see OTLT and EAV: the two big design mistakes all beginners make

Hi,

thanks for the quick reply.

The problem is that I don't know the number and type of properties in advance. I need a data desig that enables me to let the user specify specific properties.

Consider for instance a car parts product catalog, where all products share common properties such as name, description. The products also have product specific properties. For a exhausts I need to store the the diameter, but for the multi cd player I need to store the number of cd's.