java - Best Way to Design database for AutoComplete Functionality -

java - Best Way to Design database for AutoComplete Functionality -

best way design database autocomplete functionality. have next usecase(using technologies java,spring,hibernate,mysql):

i have professional details associated skills(many many relationship).

public class professionaldetailsdto { private integer professionalid; //many many private list<skillsdto> profileskills; setter , getter ... } public class skillsdto { private string skillname; private integer skillrating; private integer skillid; setter , getter }

now ui when type type skills should valid skills suggestion (like java,hibernate).people might save dummy skills or abrupt value (e.g xyz )in skill table ,so don't want show suggestion in autocomplete.

i can think of next approaches right now:-

approach 1 : having 4 tables mentioned below

1.professionaldetails

column:professionalid

2.skills column:skillid,skillname,skillrating

3.professional_skills column:skillid,professionalid

4. predefinedskill column:id,skillname

is bestpractice have 1 more table predefined skills used autosuggestion , skillname fetched predefined table stored in skills table?

cons:skillname duplicates in skills tables.

approach 2 : in approach skillrating moved professional_skills table , skillname unique in skilltable here have map skilldto tables.i have utilize annotaion such @secondarytable.

1.professionaldetails column:professionalid

2.skills column:skillid,skillname

3.professional_skills column:skillid,professionalid,,skillrating

approach 3 .same approach 2 instead of using hibernate annotation utilize query's insert professional_skills table.

please suggest me best industry practices implement above usecase in advance.

the best solution implement above utilize case(i.e many many additional columns) described in below link: http://en.wikibooks.org/wiki/java_persistence/manytomany posting this might help others.

java mysql spring hibernate database-design