There is nothing wrong with "junction" tables -- they are required for implementing many-to-many relationships. There is
really no other way to do so in SQL databases, so if you need a many-to-many
relationship, you need a table -- there is no way to avoid it.
Lookup tables -- you already seem to have a good handle on that. Use them liberally; any standard data that you'll see
again and again should go into a lookup table to avoid data integrity issues and
allow you to simplify queries. Your languages example is a great
one. If you have a lookup table, you can easily find all applicants who
speak Spanish, and you'll lower or eliminate the possibility of someone typing
in that they speak "Spnaish" (or some other misspelling), which would make it
much more difficult to identify that applicant later if you need a
Spanish-speaking candidate.
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
First off, thank you for your encouraging initial opinion of my design![]()
src="images/emoticons/smile_wink.gif">
Right now I'm too confused, before I choose to put a single skills column
which will hold all of the applicant's skills, l was thinking to pick some
general key skills and put it in a separate table, then filling that table
with these pre-determined skills, then I could use a junction table to link
those skills with applicants' resumes as there would be a many-to-many
relationship between resumes and skills. Then I was facing a serious
cumbersome in another tables; like the Resumes_Languages junction table. I
used this table to reflect the m-m relationship between the resumes table and
languages table which has some pre-determined languages to specify the
proficiency of every language selected per applicant, so I decided to create
yet another table named LanguageProficiency and linked it to a FK in the
languages table: see href="http://wunex.blogspot.com/default.htm">http://wunex.blogspot.com
Someone told me that I should avoid junction tables and started to talk
about cross-reference and things I don't understand, so if he's right what
wrong with junction tables and when I should/shouldn't use them.
Another question is about lookup tables when I should use them and when I
shouldn't
