0

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 BB, another 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?

closed as too broad by battery.cord, Himanshu, Reshma, Pranay Jaiswal, glls Jan 24 at 17:43

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

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?

You should not try to design a 1:M relationship here between a Product and Product Property. This will lead to a normalized table structure which is not required here. Your use case is that of a typical data model design where you will want to create a "de-normalized" table structure, at least in this case.

Based on your example, all the Property Type here should be basically different fields on your Product object representing respective Product Type. In the example above, AA-BB-CC:TT all become as individual fields on the Product object. What you will need is to upfront identify the type of properties your Product can be of. Knowing the attributes/property for any object should be the pre-requisite for any good design.

In case you are not able to identify all the probable properties for the Product, you can simply have another say text area field to capture any such details, while the primary property types already available on the object.

This model will also have significant advantage on performance over long run and with larger amount of data, viz., while running reports, fetching records using SOQL, etc.

  • There is one problem though. I may have a product and another product. While the former will have some properties, the latter may not have them. So, why should I have those properties on every product? And there are quite significant number of different properties present. Thank you. – iamyourfriend Jan 23 at 9:50
  • Theoretically all properties categorize a product someway or other. So if one has one property and the other doesn’t, that’s still fine. If you can group most frequent properties in one, then you can use record types here. Consider an example (very basic) where the Name field has First Name, Middle Name and Last Name, not everyone say has Middle Name. It just depends on how you want to design your data model considering all the aspect which solves your use case. In your case I will again recommend not to normalize the objects. – Jayant Das Jan 23 at 13:43
  • What does it mean to normalize the objects? Thank you. – iamyourfriend Jan 23 at 15:37
  • Its the process of structuring your data. You can find more on this topic here and here. Hope it helps. – Jayant Das Jan 23 at 15:40
  • Oh. Thank you a lot for that. – iamyourfriend Jan 23 at 17:01

Not the answer you're looking for? Browse other questions tagged or ask your own question.