[eluser]obiron2[/eluser]
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.
Obiron