Apex Code Development You need to sign in to do that
Don't have an account?
vir123 How to define custom object relationships correctly
Is it a best practice in force.com to normalize data? Will there be disadvantages or complexities due to normalization?
For e.g. say I have a team object and under that employee object and employee object will have multiple child objects like employeeQulifications, employeeSkills, employeeRelationships, etc . Now is this a right way to do it? Or are there any alternatives?
If I do it like the way I have suggested and if I want to create a report type to query teams with all employee attributes (employeeQulifications, employeeSkills, etc) I wont be able to do that as you can select objects in the report type screen only vertically. For e.g I can select team, employee but after that I can select only one employees child object not all.
This is the summary of my example:
team - custom object
employee - custom object, have a lookup relation to employee
employeeQulifications - custom object, have a master detail relation to emploee
employeeSkills - custom object, have a master detail relation to emploee
employeeRelationships - custom object, have a master detail relation to emploee
Is this the correct way of defining object structure? As I have explained above this will have an impact on report types. Other option I can see to get fields of these employeeQulifications, employeeSkills, employeeRelationships is to link these again to employee as a lookup relation so that I can retrieve these from report type --> lookup fields from employee object. But is that a correct way?
Since employeeQulifications, employeeSkills, employeeRelationships have multiple records for a single employee I have to put a master-detail record to employee from these objects. On the other hand since I can't get all these in a single report type only solution is again to put lookup fileld to employee from all these three objects. Is it correct to put links from both sides like that. I'm really confused about this.
What is the best method of designing a data model in force.com?
1. Correct
2. Can you not use dependent picklists?
3. You don't have to put all the related information into a single text area. We have used repeated elements to handle this. I.e. we allow up to 10 qualifications, and there is qual_year_1__c, qual_year_2__c etc. You do this for each field that you need to report on. It means that your criteria for a report can get ugly, in which case you may look at a field that has consolidated information updated by trigger.
I'm not necessarily suggesting this is the way to do it, I'm simply telling you how we have resolved this problem in the past, taking into account the requirements of the particular customers. The last part of that sentence is the important bit - if you customers can't report or access data in the way that they want, it doesn't matter how elegant your data design is (or you think it is), its not fit for purpose.
There are always compromises when designing your data model. You should obviously start out with the best intentions, but also accepting that you may not be able to adhere to these principles in all cases.
If I had a one size fits all solution for this I suspect I'd be a rich man, or at least in high demand!
All Answers
This is a situation we've hit a number of times with our customers. We usually end up with a shallow graph of wide objects, rather than a deep graph of narrow objects. I have a vague recollection of reading a Salesforce document that recommended this route, but I may be imagining that to support my position!
We had a similar requirement to that which you describe below, and after some weeks of struggle we changed a number of the related objects into a combination of multi-select list and associated custom fields. We then broke each of these out into a separate section of the page. We had a number of discussions with the data architect about this, but the bottom line was that we couldn't support the user reporting requirements if we didn't do it that way.
Thanks for the reply. But will that really solve the problem? According to the example I have posted lets try to implement what you have suggested to one object (say employeeQualification):
1/ There won't be an object called employeeQualification.
2/ Employee object will have multi select picklist to select "Qulification Type" (e.g Degree), "Qulification Field" (e. Finance), etc:
[Disadvantage: All these will be hardcoded, Cannot say which qualification field comes under which qualification type]
3/ There will be a text area where you can specify multipe qulification descriptions. In the same field we may have to specify other attibute such as "Qualified Year".
[Disadvantage: You cannot get reports group by some attributes (e.g. Qualified Year)]
Likewise we have to delete all child objects (in my example employeeSkills and employeeExperience) and make employee object wider.
Is this what you are suggesting? Please correct me if I'm wrong. If what you are suggesting is something else please explain it by using the same example so that even another person who is having the same problem can easily understand. But is this a good way? There should be a better way of doing this I suppose.
1. Correct
2. Can you not use dependent picklists?
3. You don't have to put all the related information into a single text area. We have used repeated elements to handle this. I.e. we allow up to 10 qualifications, and there is qual_year_1__c, qual_year_2__c etc. You do this for each field that you need to report on. It means that your criteria for a report can get ugly, in which case you may look at a field that has consolidated information updated by trigger.
I'm not necessarily suggesting this is the way to do it, I'm simply telling you how we have resolved this problem in the past, taking into account the requirements of the particular customers. The last part of that sentence is the important bit - if you customers can't report or access data in the way that they want, it doesn't matter how elegant your data design is (or you think it is), its not fit for purpose.
There are always compromises when designing your data model. You should obviously start out with the best intentions, but also accepting that you may not be able to adhere to these principles in all cases.
If I had a one size fits all solution for this I suspect I'd be a rich man, or at least in high demand!
Yes may be you are correct :smileyhappy:
For your second point, yes I can use dependent picklists. My only worry is that I have to hard code data....but as you say "There are always compromises when designing your data model" :smileyhappy:
Even I don't see another better way of doing it....my only concern was that I don't see another solution due to my limited knowledge in force.com. I think what you are suggesting is correct.
As explained in the first comment, other option I can see is to put master-detail relation to employee object from each child object (employeeQulifications, employeeSkills, employeeRelationships) and agin lookup relationship from employee object to all these child objects (so that I can retrieve these from report type --> lookup fields from employee object. ) But I still don't know whether it is correct. I mean it may raise some other issues.
The problem is that you can only relate one child object to the parent object via a lookup field, so you'd need a lookup per qualification (for example) and I'd expect you'd run into some limits on that.
There is talk of reporting being able to handle cross object scenarios, but I don't think that there is a date for when this will be available, or how much of this it will handle.
Another customer chose to use the normalized data route and build custom visualforce reports. This was because the volume of child objects made it impractical to embed them all in the parent object. That's an expensive route though.
Another route is to export the data to an external reporting/data warehousing system. I don't think we've implemented this for anyone yet, but there are packages on the app exchange to do this.
Thanks for suggested alternatives. Thats a great help.
By the way I have a question about the first statment you have made in the last comment:
"The problem is that you can only relate one child object to the parent object via a lookup field, so you'd need a lookup per qualification (for example) and I'd expect you'd run into some limits on that."
What I tried was:
1/ employeeQualification have master-detail for employee and lookup for qualification type and qualification filed. This is the object which hold employee wise qualifications.
2/ Then again from the employee object put a lookup relation to employeeQualification. (You can hide this in the employee page layout since we already have the related list for employee qualifications due to 1st step)
3/ I can do the same thing for other child objects (employeeExperience and employeeSkills) without any issue. I didnt get any limitation issues while doing this.
Only problem I had was whether it is correct to do it like that. I mean if we put a master detail relation from object1 to object2, we just dont go and put again a lookup realtion from object2 to object1. But by doing this I can solve the reporting issue. But I'm not sure whether it is correct and will lead to other issues.
But if in step 2 you add a lookup to employee qualification, that will only point to a single qualification. If your employee has several, you will need a lookup per qualification. If you only have a one to one relationship that will work, but if you have multiple qualifications, experience, skills) then it may not, as there is a limit to the number of lookup fields than an sobject can have.
You didnt unserstand it correctly. As the first step I put a master-detail relation from employeeQualification to employee. After doing this employeeQualification will become a related list of employee so that I can add any number of employeeQualifications to employee. The only issue here is the reporting problem as I have explained above. So here I do a trick. That is, in addition to the master-detail realtion, I put another lookup relation from employee to employeeQualifications. This is purely to satisfy reporting issue. This is what I'm not sure as I'm doing something unnessaray. If I say it again as a summary:
1/ I put a master-detail relation from employeeQualification to employee
2/ I put another lookup relation from employee to employeeQualifications (In addition to the step 1 relationship. This is just to satisfy reporting issue) - Here I can hide this from employee page layout as I have elaready inserted the relaed list from employeeQualifications.
By doing it like above I can overcome reporting issue as well I can enter multiple employeeQualifications to a single employee. My concern is whether it is correct to do it that way.
When you create a lookup field on a record, that will point at a single instance of a record.
1/ I put a master-detail relation from employeeQualification to employee
2/ I put another lookup relation from employee to employeeQualifications
In step 1, as the relationship goes from child to parent, you can have multiple employee qualifications per employee, which appears as a related list.
In step 2, you have a lookup from the employee to a single employee qualification. I don't see how this handles cross object reporting, it allows you to report against one other qualification, not all that the employee has.
Yes you are correct. Even though employeeQualification fields are displayed as lookup fields in the report type, when I create a report it shows only one employeeQualification. So it is not a workaround. I think the only solution I found up to now is what you suggested.