Hi all ,

we all learn in college that we have to normalize our DB ER

and one rule is to make any multi value attribute in different table with a Primary key

like when we want store person's info

we put his ID , NAME , ... in table

and his skills in diff table

like person with id = jp3003

has skills like :

java , php , C# , UML

we store it as 4 records in the Person-Skill table

;
i wonder why don't we store it in one field in the Person table !

like : java php c# UML

with space in between and store them as TEXT type !?

is not that better for performance !?

since when we want to display the Person's info later

we will need to read one table only ! (Person)

not reading two table Person + Person-Skill

and if we want search for people who has the java skill

can't we use "like" operator ? with a pattern like " java "

Is it better for performance ?

will the use of TEXT type or Like operator affect the performance ?

thanks

That is a good question.

There are a number of reasons why this is done. One of them is that if all the skills are stored in one text field it is much harder to search for a skill. When the skills are stored in a seperate table you can easily get all people that have the skill 'Java'.

select * from users natural join skills where skill='Java'

If the skills are in one text field you would have to get all users, explode the skill field of each user, then determine for each user if the have the skill 'Java'. The database is much quicker at doing this. Joins can be a very efficient way of getting only the data you want.

Yes you can probably use the 'like' operator but joins are much more efficient.

Something else I just thought of is if it is a seperate table you can probably use varchar instead of text as the field type for the skill. Someone will have to confirm (or refute) this but I think that will take up less space in the long run for a very large database.

I know there are other reasons so I hope other people post more.