Assign multiple categories to one DB object in ASP.net MVC

Question

Currently I have a database object, "vacancies" which has a column (foreign key) "CareerLevels".

The table "CareerLevels" in my DB has a dynamic amount of CareerLevels, say 3 for example.

Now I have my Vacancy creation wizard where, at a certain point, I have a page with some checkboxes for the CareerLevels. I want to be able to assign multiple CareerLevels to my Vacancy. For general checkbox generation and handling I use the approach in this topic.

So we want to have something like:

Vacancy x having careerlevels y and z. where y and z are in the DB table CareerLevels.

Known method

I know I can do this by creating a table like "VacanciesHavingCareerLevel" with Foreign Keys "Vacancy_id" and "CareerLevels_id" just like the UsersInRole in the standard asp.net mvc framework. This way I have a joint table keeping track of multiple careerlevels belonging to my vacancy. In code you can fetch this and work with it.

Though this means that for every variable having multiple options belonging to your DB object (vacancies) you will have such a table, resulting, in my case, in 10-15 extra tables.

The Problem

I have not only CareerLevels but also for instance EducationLevels, JobTypes and more. This way I would have to create multiple tables like VacanciesJobtypes, VacanciesEducationLevels and VacanciesCareerLevels. I also have multiple objects having those things. I have my profiles having EducationLevels, Jobtypes, CareerLevels etc. Meaning all my tables just generated times 2, this time having ProfileCareerLevels etc.

The real question

Is this the right way to go or should I adopt another model? If another model excist, what are well known models for this?

Answer 1

This seems to me like a simple many-to-many relationship. A "Vacancy" can have one or more related CareerLevels. If you want to model a many-to-many relationship you must have a join table, as you suggested.

I don't see a problem having additional tables in your schema to support this. Ensure that the indexes and constraints are setup up properly- for example, you may want to make VacancyID and CareerlevelID a composite primary key in your join table.

Answer 2

Your original design allows one career level to have many vacancies.

If you want one vacancy to have many career levels, then the foreign key is in the wrong table, should be on the "many side", like this:

Problem with this is that that a carrier level belongs to one vacancy only, so a better solution would be:

There is nothing wrong with having "many tables" in a DB. The ER model allows you to manage entities (vacancies, career levels, education) independently, and then assign (manage) relationships between those entities. If joins are perceived as a problem, simply design several views.