Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Posts
    8

    Unanswered: storing values in different tables based on data type

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    8
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •