Database layout

[eluser]hykoh[/eluser]
Hi,

my question is not CI related; but i love this community in here

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

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 ?

Any ideas ???

Thanks

[eluser]brianw1975[/eluser]
let me ask you this.... why are you using a database table to describe your eye color? that's something that could be hard coded.... i mean, it's not like someone is going to choose 'Maroon' or 'Electric Green' adde: what i was trying to say is: you aren't going to be adding and removing these things on a regular basis so why make it so hard on yourself?

it's pretty much going to be green, blue, brown, black, hazel, and one or two others that i can't think of...

a more appropriate table might be something along the lines of

model_properties:
id
model_id
property_id
property_value

and a property table with:
id
property_name (hair color, eye color, skin tone, etc)

then to get a models properties you go $this->db->get_where("model_properties",array("model_id"=>$your_model_id));

and you have the models properties without all the hassle and still have the ability to add/remove properties in the admin section.

just a suggestion.

[eluser]hykoh[/eluser]
the reason that i tried to solve it in this way is that i can select the eye colors etc. global from everywhere (search form, profile and so on). another reason is the multilanguage support for each datafield.

i'll try to build up on ur suggestion, thanks

[eluser]ray73864[/eluser]
one word: enum

MySQL supports the enum datatype for fields, which means the value of that field *has* to be one of the enum options.

[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

[eluser]Hockeychap[/eluser]
Both of the database designs will work.

Your initial structure looks like a data warehouse structure (one main fact table with lots of small "dimension" tables). It will work but as you have spotted it needs a substantial number of joins and mysql doesn't, at the moment, support any specific optimisations for snowflake schemas. The upside however is that maintenance of the data is a lot easier and retrieval can usually be done using views .

The 2nd structure is much more asset / object structured. Will also work but will make the sql statements more complex. There's also a well known bug in the "IN" statement of mysql (right upto version 6.x). In essence the outer part of the IN is evaluated before the inner restriction is applied. To you and I this means that at large table volumes (10 - 100s of thousands of records) you will see substantial slowdowns.

Having used both styles of database structure for clients, I found it came down to the system use. I used the dw structure for drilling into and through detailed transaction reports (~2 million rows a month).

I used the object based structure for a photograph library (again of around 2 million assets with differning metadata) as it was more easily manipulated from a transactional point of view.

So, the long and the short of it, to me it depends on the use of the data - transactional vs. reporting.

Cheers
Justin

[eluser]hykoh[/eluser]
And how can I get it in one row then ?

I think its not the best way to read it out about a foreach or other loops.

I want to display it like

Code:

Hair color: <?=$hairColor;?>
Eye color: <?=$eyeColor;?>
... etc.

dont want to search several arrays for the right values.