I've seen countless orgs where objects and fields have been created just because someone said hey, can we have a field for X? This can lead to a number of problems, ranging from data skew to terribly slow code. Thankfully, a little forethought can prevent this. Data modeling can be hard to do, especially if you're struggling with conflicting stakeholders and their understandings of the platform. I sometimes find it helpful to take a page from consulting and ask the stakeholders to collaboratively answer three questions. First, ask what is the clearest, most succinct statement of the problem. Secondly, ask what is the clearest, most succinct statement of the ideal solution. Lastly, ask if the stakeholders have any ideas on implementing the ideal solution. These questions need to be answered by the stakeholders, but they need to know going in that you're not just going to adopt the first solution they come up with. Oftentimes, adding a field to an object is no big deal. However, changing what field holds a key value, or replacing a relationship with a junction object, poses immediate risk to all declaratively developed functionality and all code, as well as reports and dashboards. While you'll be able to easily identify the pieces as they break in your sandbox (you are doing this in your sandbox, right?), you'll need to be able to count the cost ahead of time. Identifying ballpark figures for how long it will take you to accomplish the addition, or modification, of a field or object not only demonstrates mastery of the system to management, it helps keep you from having to work long hours. Being able to tell manager Mark that this will take either three days, or three weeks, for example, helps everyone understand the magnitude of the problem before you start. It sets proper expectations for workload, because you can either refactor this relationship into a junction object, migrate all the data and deploy it over the next two weeks, or implement the requested feature X. Not being able to give such estimates leads to feature X and the junction object being due at the same time.

In order to develop the ability to confidently alter the system at an object-level, we have to architect it. So, what's the difference between engineering and architecture here? Simple: architects are responsible for designing and documenting systems, whereas engineers are responsible for building the system. Ideally, there is a good deal of interplay between the architects and engineers. The best teams I've worked on have had many engineers who've collaborated with the architect. The key here isn't who's writing what code, but who's responsible for providing the blueprints of the system to the engineers. Without that blueprint documentation there's no 10, 000 foot overview demonstrating the relationships between objects. This is absolutely essential for remotely accurate estimates of object-level refactoring jobs.

Like editors and operating systems, many wars have been fought online over object documentation tools. Put simply, pick one and use it. I've seen everything from high-resolution pictures of whiteboards to PowerPoint files full of painstakingly arranged boxes, arrows, and text. In a pinch, even a collection of screenshots from the schema builder are useful. Until recently, I had yet to discover a data modeling and object documentation tool I truly loved; they all feel entirely too manual and frustratingly slow. Recently, however, I came across an interesting project from fellow MVP Andrew Fawcett. In 2013, he released a native platform application for inspecting and calculating UML drawings of your org (http://andyinthecloud.com/2014/03/17/going-native-with-the-apex-uml-tool-and-tooling-api/). His tool, the Apex UML Tool, has a few benefits over traditional tools. First, it's able to generate true UML drawings showing not only objects and relationships, but object methods, as well. Secondly, it actually generates these diagrams from your existing code. Don't already have architecture diagrams for your org? This tool will generate them for you! I encourage you to give it a try, especially if you don't already have diagrams demonstrating objects, their relationships, and fields.

Using these architecture diagrams, you can rapidly identify what objects might be affected by a proposed change. Even more importantly, they allow you to ask engineering questions about the proposed change. Does the proposed solution nest objects more than five levels deep? If so, you can't always assume one query will be able to capture all the data. Does the architecture complicate reporting to the point where Apex will be required to meaningfully determine business critical information? Finally, these types of documents are fantastic for helping you and your team discuss, adopt, or rule out the stakeholders' proposed solutions. For instance, tracking a client's reception of an e-mail document could be solved by an account record checkbox, but sending a new document every week would soon run you out of custom fields.

Having the ability to look at an architectural diagram and know in a flash that you could create the account e-mail delivery tracking object as a child of the account helps to not only provide the ideal solution for the stakeholders, but the ideal solution for the org overall.

In order to evaluate the potential change, the entire team needs to be on the same page for relationship types and junction objects. I've heard arguments for biasing towards lookup relationships, and I've heard arguments for biasing towards master-detail relationships. I tend to ask myself whether or not the child object provides meaningful data outside of its relationship to its parent object. If the answer is yes, I bias towards using a lookup relationship; otherwise, I bias towards master-detail relationships. In a way, this is for ease-of-use as master-detail child records are automatically deleted when the master record is deleted.

There are times, however, where that rubric fails, notably the case of the many-to-many relationship. Classically demonstrated by orders and products, we model the understanding that an order contains between one and N products and that each product can be sold on multiple orders. Defining the relationship between order and product requires a many-to-many relationship. Thankfully, Salesforce provides one and only one way of building a many-to-many relationship: junction objects. A junction object models records that are the child record of two master records. Defining a junction object is actually pretty trivial; create an object and order _m2m_product with two master-detail relationships. In the case of our example, you'd create one master-detail relationship to the order and a second to the product. Because they are both master-detail, we must populate both relationships in order to save the record. Thus, every order_m2m_product contains a reference to both the order and the product objects.

If you stop and think about it, the way junction objects work is pretty intuitive. What's not immediately obvious is that junction object records aren't limited to just the two references to parent objects. In the case of our order and product example, the junction object may also hold, for instance, a lookup reference to the contact making the order, or the account the order is to ship to. Additionally, the junction object can have notes, attachments, and activities attached. The junction object also becomes the center of query attention. Because we can easily query parent records information with dot notation, it's simpler to query a junction object instead of querying orders and then querying products. Indeed, you can write one query and retrieve order information, including data from the order's parent account record, while also gathering product information. Something a bit like this:

SELECT Order__r.Account__r.Name, Order__r.Id, Order__r.Contact__r.FirstName, Product__r.Id, Product__r.Name, Product__r.ProductFamily__r.Name FROM Order_m2m_Product WHERE Order__r.Account__r.Id = 'awesomeId'

In the preceding query, we're pulling data not only from our junction object's immediate parents, but the parent's parent record, as well. Additionally, our where clause is built out based on the order's account ID. This would effectively give us all orders and products that a particular account has made. Including a lookup relationship to contact on our junction object would allow us to query for all orders and products ordered by a given person.

The power and utility of junction objects is virtually limitless, but that power comes at a cost. As soon as a junction object is created, the cost for refactoring that data model goes way up. Because of this, I always ask for and document why this relationship between objects must be a many-to-many relationship. Bias away from junction objects and toward master-detail and lookup relationships, and only ever create junction objects where a many-to-many relationship is clearly needed. While this may seem obvious, it's easy to inadvertently generate a junction object simply by adding a second master-detail relationship where a lookup relationship should have been used.

Modeling your data and your class's methods is easily more of an architecture chore than an interesting engineering challenge, yet this kind of documentation is the single best way to prevent accidental junction objects and data skew, not to mention the best possible way to accurately estimate the time-changing the architecture will take.