Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    2

    Question Unanswered: Database structure / userdata with attributes

    Hi,

    I try to create a database for a model agency page. There must be some attributes like hair color, eye color and so on in several languages ...

    My idea:

    Code:
    tbl_model_data:
    - id
    .....
    .....
    - eyeColorID
    - hairColorID
    - skinColorID
    .....
    But there are up to ~5 ID-related values then - so 5 table joins like this

    Code:
    SELECT *
    FROM tbl_model_data
    LEFT JOIN tbl_hair_colors
    ON tbl_hair_colors.id = tbl_model_data.eyeColorID
    LEFT JOIN tbl_eye_colors
    ON tbl_eye_colors.id = tbl_model_data.eyeColorID
    LEFT JOIN tbl_skin_colors
    ON tbl_skin_colors.id = tbl_model_data.skinColorID
    ....
    In any case it seems like it is not the best way to solve it, isnt it ?

    In another board i got another suggestion:

    Code:
    I would reconfigure the database as follows;
    
    tbl_models - holds the model ID and basic details - Name, DOB, Agency etc.
    
    tbl_modelattributes - model_id,attribute_type,attribute_value
    
    tbl_attributetype - attribute_type, description
    
    tbl_attributes - attribute_type,attribute_value,attribute_description
    
    This is a useful format for what are basically lookup values.  You only need 1 table and therefore one maintenance routine.
    
    Your selections all follow the same format
    
    select MOD.*, ATT.description from tbl_models MOD
    join tbl_model_attributes MA on MOD.model_id = MA.model_id
    join tbl_attibutes ATT on ATT.attribute_type = MA.attribute_type
    join tbl_attributetype TYPE on TYPE.attribute_type =ATT.attribute_type
    where TYPE.description = ‘HAIR’
    and ATT.attribute.value = 4
    
    
    If you need to find models that satisfy more than one option (e.g. brown hair and green eyes)
    
    select MOD.*, ATT.description from tbl_models MOD
    join tbl_model_attributes MA on MOD.model_id = MA.model_id
    join tbl_attibutes ATT on ATT.attribute_type = MA.attribute_type
    join tbl_attributetype TYPE on TYPE.attribute_type =ATT.attribute_type
    where TYPE.description = ‘HAIR’
    and ATT.attributevalue = 4 and
    MOD.model_id in
    ( select MOD.model_id from tbl_models MOD
    join tbl_model_attributes MA on MOD.model_id = MA.model_id
    join tbl_attibutes ATT on ATT.attribute_type = MA.attribute_type
    join tbl_attributetype TYPE on TYPE.attribute_type =ATT.attribute_type
    where TYPE.description = ‘EYES’
    and ATT.attributevalue = 2
    )
    
    This basicallys says, get me all the model data where hair is brown and the model_id is in the list of models with green eyes
    
    
    Note in the second select the inner query only returns the model_id, otherwise you will get an error message.
    
    If you are building a models database, you will probably also have international users so you should look at normalising the descriptions of types and attributes for multi lingual.
    
    In this instance you would move the description from the table and replace it with a language table.
    
    tbl_language - type_id,attribute_id,language,description
    
    
    This can then be used to build a multi-lingual form and search results.
    But i get one row per attribute then .... it would be fine if it's possible to get all attributes in one row ..

    Any ideas ???

    Thanks for help

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    my idea is this: you do ~not~ need to generalize to the full-blown entity-attribute-value (EAV) model

    you're making this a lot more difficult than it needs to be

    start simple, like this:

    CREATE TABLE models
    ( id INTEGER
    , haircolour VARCHAR(99)
    , eyecolour VARCHAR(99)
    , ...
    )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by hykoh
    Any ideas ???
    Using an attribute table like this is called EAV. This method is generally frowned upon in this forum but it can be useful when the attributes describing things can change or if you want to handle all attributes in pretty much the same way. If the attributes are unlikely to change much over time then I'd avoid taking this route.

    If you still want to go down this route then I'd advise having a lookup table of valid parameters (ie "hair colour") and another table holding the valid responses for each parameter - then checking that values are valid before being stored in the attribute table. The normal field values ie name, sex, DOB etc would still be stored in the model table.

    I personally wouldn't use surrogate keys ie 4 for "Brown hair" as it just complicates your design and code. I'd just store the values using your preferred language and then translate at the point of displaying to your users. So to search the database for models with brown hair and green eyes :
    Code:
    select  name
    from    Models m
    where   exists( select 1 from Attributes a 
                  where a.id = m.id and a.type = "hair colour" and a.value = "brown" )
            and exists( select 1 from Attributes a 
                  where a.id = m.id and a.type = "eye colour" and a.value = "green" )
    or you can make the code easier to read by using functions to read the attribute table :
    Code:
    select  name 
    from    Models
    where   get_attribute( id, "hair colour" ) = "brown"
            and get_attribute( id, "eye colour" ) = "green"
    Then you could then add a function that uses a translation table to convert any simple text ie "hair colour", "brown", "green" to each language you cover. It might be a good idea to log any missing text so it can be added at your convenience. This way you could produce a simpler system in your main language and then add the language support later. So to view all the attributes of a model in French :
    Code:
    select  translate( "French", a.type ), translate( "French", a.value )
    from    Models m, Attributes a 
    where   m.id = 1234
            and a.id = m.id
    This isn't the most efficient solution but it does make the system easy to understand and build. It will also be quite easy to look after ie it's easy to add new model attributes or new languages etc. As all the attributes are treated in the same way then it should be easy build the screens as well. Assuming you don't have millions of models on your books so it should be more than fast enough.

    Just my 2c. There is a lot to argue about in the above, ie using EAV at all, not using surrogate keys, a separate translation table etc but at least it's got the ball rolling.

    EDIT : tidied up code
    Last edited by mike_bike_kite; 02-18-09 at 11:01.

  4. #4
    Join Date
    Feb 2009
    Posts
    2
    hey mike,

    i think it's not the best way ... it's to much validating and complicate translating stuff at all :/

    the best solution should be the object orientated version in my eyes:

    Code:
    tbl_models - holds the model ID and basic details - Name, DOB, Agency etc.
    
    tbl_modelattributes - model_id,attribute_type,attribute_value
    
    tbl_attributetype - attribute_type, description
    
    tbl_attributes - attribute_type,attribute_value,attribute_description
    i can be sure that the data is valid and get easy the value in different languages.

    I'm still open for other suggestions =)

    thanks alot

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by hykoh
    I'm still open for other suggestions =)
    you mean, besides the suggestion you received on post #2?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by hykoh
    hey mike,

    i think it's not the best way ... it's to much validating and complicate translating stuff at all :/

    the best solution should be the object orientated version in my eyes:
    Code:
    tbl_models - holds the model ID and basic details - Name, DOB, Agency etc.
    
    tbl_modelattributes - model_id,attribute_type,attribute_value
    
    tbl_attributetype - attribute_type, description
    
    tbl_attributes - attribute_type,attribute_value,attribute_description
    No problem but interestingly the table and fields you list look almost like the tables and fields I'd use (and described) - weird. Is this what you'd call an object orientated design these days? If you're still in the design stage then I'd loose the "tbl_" at the start of each table name and perhaps use capitalisation to distinguish table names (ie ModelAttributes).
    Last edited by mike_bike_kite; 02-19-09 at 15:08.

Posting Permissions

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