What is the best way to configure a many-to-many relationship with one side being a fixed set of records, which may have different types?
I think it will be a lot easier to understand what my question is about after I will tell you about my use case.
I have an object Product (to clarify, this just allows me to store records of type Product and further on instead of using two words
Product record I will use only a word product). Now each product is represented using different properties. For instance we can have a product
AA-BB-CC:TT, here the record conveys the following information: the value of one property of the product is
AA, the value of another property is
CC and the value of the last property is
TT. For any taken product there are can be any number of any properties.
Now in order to store the products (at the first glance) we could come up with the following solution: let us just create an object for each property and link it to our Product object through a master-detail relationship, where a product will be master.
The solution actually accomplishes what I need. But it is very easy to optimize it if we know beforehand the possible set of properties and the values for those properties. In order to optimize the solution we will first take each possible property and create an object(1) for the property. Then we will take all the possible values for the current property and create a record for each of those values, the records will be of object(1) type. Now we will use an intermediary object which will provide a way to connect a product with all the product properties. The intermediary object will have only two fields, which will be master-detail fields, one for the connection with a product and one for the connection with the record which represents a property value. The intermediary object will be a detail in both relations.
And after the solution applied we do not duplicate values of properties as we did before. Now for each (property,value) pair we have exactly one record. Also, one of the benefits is that it is easier to maintain the setup and consistency: updating the value of the property in one place will update it everywhere where the value is used.
Should I just create the respective objects (which will represent properties of my product) and respective records (which will represent values of the properties) manually and move such a setup around (e.g. move the objects and records to another org in a managed package)? Or, maybe, there is a better way to accomplish this?