Database Design Diagram - From Many to Many Classifications / Categorized Tags

Question

I have an existing database project that stores jobs.

In the table "Vacancy" there are a number of fixed fields for all customers, such as "Title", "Description", "Salary Range".

There is an EAV design for the "Custom" fields that customers can configure themselves, for example, "Manager Name", "Business Hours". Field names are stored in the ClientText table and stored in the VacancyClientText table using VacancyId, ClientTextId, and Value.

Finally, there is a lot for many EAV projects for custom labeling / categorization of vacancies with things like “Locations / Offices”, a vacancy, a list of required skills. This is stored as a "ClientCategory" table, which lists the tag types, "Locations, Skills", a "ClientCategoryItem" table, which lists the valid values ​​for each category, for example, "London, Paris, New York, Rome", "C # VB, PHP, Python. " Finally, there is a table called “VacancyClientCategoryItem” with VacancyId and ClientCategoryItemId for each of the selected items for the vacancy.

There is no limit to the number of custom fields or custom categories that a client can add.

---

Now I am developing a new system, which is very similar to the existing system, however I have the opportunity to limit the number of custom fields that the Client can have, and it is created from scratch, so I have no problems with the past.

For custom fields, my solution is simple, I have 5 additional columns in the job table called CustomField1-5. This removes one of the EAV constructs.

It is with the tag / categorization that I am struggling with. If I limit the client to the presence of 5 categories / types of tags. Should I create 5 tables that list the possible values ​​for "CustomCategoryItems1-5", and then another 5 tables "many of many" "VacancyCustomCategoryItem1-5"

This will cause 10 tables to work with the same storage as the three tables in the existing system.

In addition, if (God forbid) the requirements change in that I need 6 user categories, and not 5, this will lead to a big change in the code.

---

Therefore, can anyone suggest any database design patterns that would be more suitable for storing such data. I am happy to adhere to the EAV approach, but the existing system is faced with all the usual performance problems and complex queries associated with this design.

Any advice / suggestions are greatly appreciated.

The DBMS used is SQL Server 2005, however 2008 is an option if required for any particular template.

Answer 1

Have you thought about using an XML column? You can enforce all your restrictions declaratively through XSL.

Instead of EAV, there is one column with XML data validated by a schema (or a set of schemas).

Answer 2

Look at this question / answer; describes the picture of observation. It uses five tables and can be implemented in the "standard" RDBMS - Sql Server 2005. There are no restrictions on the number of custom properties (observations) that objects can have.

EDIT

If properties require tags (categories), look at that .

Answer 3

Why not save custom fields in a key value table?

| vacancy ID | CustomFieldType | CustomFieldValue | 

Then enter the auxiliary tables that list the possible values ​​for each type (1 table) and may be possible types for the vacancy type (this is apparently the original client category)