Linking a product to several categories - request for ideas on how to solve this
I’m looking for ideas on how to prepare a dataset to make it easy to use. I can't hide the fact that I've focused on probably the wrong solution so I'm stuck and I'm asking for help/ideas :) Business problem: 1) There are products in the database 2) There are 25 categories describing the products (there may be many more in the future) 3) Each product can have 4 different categories assigned to it: one main category and 3 additional ones (ideally I would like to prepare an even more advanced solution: each of these categories has a priority from 1 to 4) 4) The user can search for products by these categories, but also these categories are used to display the corresponding images next to their descriptions (indicating the attribute) I’m wondering firstly how the entities should be structured and what the relationships between them should look like, given that the platform administrator can add (but not delete) new categories. My initial thought was to create an additional entity: "a set of categories", which would contain 4 attributes ('Category1', 'Category2', 'Category3', 'Category4'), and each of these attributes would be associated with an entity containing a list of these categories. Each category also has a description, so additionally for this reason I cannot use enumeration. However, in this situation I have the problem of how to firstly prepare the form for the user and secondly how to finally assign a specific category to a specific attribute. Perhaps even taking into account those priorities mentioned above. Enumeration is out of the question because of the need to change categories dynamically. The simplie model I had in mind you have below. Although I am pretty sure that way is wrong. Do you have any suggestions on how to make this clear and easy to use? I would be extremely grateful
Instead of using an intermediary entity (CategoriesSet), you could just create 4 associations between Product and Category, e. g. Product_CategoryMain, Product_CategoryOptionalOne, ...Two, and …Three.
You can add a delete constraint to the domainmodel so that a Category can only be deleted if it is not associated to a Product via one of these 4 associations.
Using the associations allows to use the the standard reference selector widget.