Multivalued attributes in MySQL

Question

I am working on a database in MySQL to show multivalued attributes. I am trying to find a way to create a parent and child table. The idea that I am working with is having an employee table and a hobby table. The hobbies in the hobby table would be considered the multivalued attributes since employees can have more than one hobby. My question would be, when creating these tables, should I use 3NF and add a 3 table to show the relation between the two or is there a way to implement this idea with simply two tables. I’m not very familiar with multivalued attributes so I need help creating the tables as far as what kind of keys each would use, as well as the end form. I believe I would need to make it in 3NF form and have the hobbies as the multivalued attribute, but have the hobby id from the hobby table as a primary key and the employee id as another primary key and then making the relational table contain both the employee id and hobby id as foreign keys referencing the other two tables. Any help or suggestions to show multivalued attributes would be greatly appreciated!

Answer 1

You have a table for employees already. It probably has a primary key we'll call employee_id.

You need a table for hobbies. It will need a primary key we'll call hobby_id.

Then, you need a way to relate employees and hobbies many-to-many. That's implemented with a third table, let's call it employees_hobbies. Using a name like that is a good idea, because the next guy to work on your code will recognize its purpose right away.

employees_hobbies should have two columns, employee_id and hobby_id. Those two columns together should be the composite primary key. Then, to confer a hobby on an employee, you add a row to employees_hobbies containing the two id values. If an employee drops a hobby, you delete the row.

If you want a list of employees showing their hobbies, you do this

 SELECT e.name, GROUP_CONCAT(h.hobbyname) hobbies
   FROM employees e
   LEFT JOIN employees_hobbies eh ON e.employee_id = eh.employee_id
   LEFT JOIN hobbies h ON eh.hobby_id = h.hobby_id
  GROUP BY e.employee_id, e.name

Use LEFT JOIN operations here to keep employees without any hobbies (all work and no play) in your list.

If you want to find the most common five hobbies and the employees doing them, try this

 SELECT COUNT(*) hobbycount, h.hobbyname, 
        GROUP_CONCAT(e.name ORDER BY e.name) people
   FROM hobbies h
   LEFT JOIN employees_hobbies eh ON h.hobby_id = eh.hobby_id
   LEFT JOIN employees e ON eh.employee_id = e.employee_id 
  GROUP BY h.hobbyname
  ORDER BY 1 DESC
  LIMIT 5

This way of handling many-to-many relationships gives you all kinds of ways of slicing and dicing your data.

MySQL is made for this sort of thing and handles it very efficiently at small scale and large, opinions to the contrary notwithstanding.

(Avoid putting a surrogate primary id key into your employees_hobbies table. It adds no value.)

Answer 2

mva is not very good for mysql .

and best way to store it depend from price you can pay and accessibility need . if you need index , because database is big , and highly loaded , then possible you will need 2 tables .

employee( id , name )
employee_hobbies ( id , employeeid , hobbyid )

but in simplest case , or if you need good accessibility, you can just add text field to employee table , store there comma separated hobbyid , and then select by FIND_IN_SET() function .

e.q. single table

employee( id , name , MVA VARCHAR(512) )

you need be sure that all ids comma separated will fit into fields , side .

SELECT * from employee where FIND_IN_SET(some_hobbyid , MVA)

advantage of this method is less queries , disadvantage - may slower then 1st .

also there is advantages for high load system , when import into sphinx ... but this is another story ...