So that’s the wine cellar I’d like to have some day…..
I’ve been thinking about creating a wine app for awhile now. We just renovated our house and I’m in the process of building a wine cellar. We currently have our wine in boxes in a room that looks well, like this
It doesn’t look like much but we’ve probably got more than 100 bottles now (along with my meat grinder and deep fryer), and that will at least double by the end of the summer. So the challenge is, how to I create an app to make that work for me? How do I make an app to match the finished product which I hope will look like this:
I actually had a bit of a wine cellar going but then it decided to be -20 most of last winter and I realized that I needed to do some insulating before investing in making it nice. So that’s what I’m doing now is watching the temperature of the room as winter rolls on through.
We live in Etobicoke, Ontario, which is quite close to one of the biggest Canadian wine regions of Niagara. You probably know if for Niagara Falls. Our wines are starting to get recognized more in the US, specifically New York, and they are actually really good, if a little more expensive than the imports for many ridiculous government reasons. So most of our stuff comes from there and we buy it numerous bottles at a time. We like going to all the different wineries and supporting local business. We also buy international wines, but I wanted to be able to also track the wineries that we frequent, what are considered the best to buy from there each year, and where they are. So to tie that all together, I’m basically looking at tracking:
- Wineries we frequent – where they are, do they have food, what they are known for, price points, etc
- Wines of interest – what I want to buy
- Wine reviews – both our own and those of others that I read
- Wines we own and that are in our cellar
I spent some extra time thinking about the data model here because it’s trickier than it looks and I think this is a good opportunity to both tie back to my original post on relationships, and also walk through my thought process in designing this model. I also had this all done and then realized I wanted to do a post on object oriented thinking, which I published last week. You don’t need to read either of them to understand this, but they will help.
So the question is, why I have each of these objects, and what relationships make sense for each. As I explained in my original post there are a few key relationship differences to consider:
- Inherits security from the parent – parents pretty much control their children’s lives in real life, just remember that.
- Permits Rollup values on the parent record from all children – you can safely say that the parent has two children.
- Master is the primary object in custom reports – you are reporting on parents with children
- Child records have their own owner and therefore security. The kid doesn’t own the bike forever, probably because they’ll forget all about it when they get a shiny new bike
- Does not permit rollup values – I find it annoying that they don’t, check out this solution once you are comfortable with more technical solutioning – still no code!
- Child is the primary object in custom reports. This would be bikes with kids for example.
Also, the little lines between the objects mean a one-to-many relationship, hence the one prong at one end and three prongs at the other. I used LucidChart for this if anyone is looking for a solution much cheaper than Viseo
So here are my thoughts on each relationship
Wineries to Wines
This one was probably the hardest to figure out conceptually. Normally this would be easy and I would say Master-Detail for sure because you can’t have a wine without a winery. The problem is that for most of the international wines that I buy I don’t care that much about the winery because I’ll most likely never go there. So I would prefer to not key in a winery for each wine. The real reason I want a Winery is so that I can use the Spring ’15 maps feature when I’m in the area. So for these reasons I’m going with a Lookup and Wineries will be a Record Type for Accounts. For those that aren’t totally clear on what record types are used for, here is a quick synopsis
- They allow you to have different page layouts for the same object, for the same profile.
- They allow you to have different Picklist values for the same field, for the same object.
- They are great to use in a workflow if you want to change the Page Layout to be all read-only once an Opportunity is won for example. You would have a workflow to say that once an Opportunity is marked Closed, do a field update on Record Type to change it, thus changing the Page Layout to one where you’ve made all fields read only.
- They do not, under any circumstances, do squat for security. Nothing. Removing a certain record type from a Profile just means they can’t create a record using that record type, it has nothing to do with visibility
- *You can use record type in Criteria Based Sharing Rules
Remember my post on Restaurants as a record type for Accounts? Layering in Wineries is a perfect case for this. They are both Accounts, they both have addresses I want to show on maps, the both employee people for example, but I would never have the same page layout for both. Solution – create record types.
I always describe record types to clients as being all about usability and end-user experience. Of course you could use the same page layout for Restaurants and Wineries, and hardward stores, and Wal-Mart locations, but then no one is happy because everyone has to compromise on the fields they see and have to use.
Again – they have nothing to do with security. Don’t make that mistake, you are better than that.
Back to the Lookup relationship, for reporting this means my standard report type would be Wines with Wineries but I’m ok with that
Wines to Reviews
This one is pretty obviously a Master-Detail relationship. The review will always be about that specific wine, and you can’t have a review without a wine. My reporting would be Wines with Reviews – which makes sense.
Wines to Our Wine Purchases
Again this is pretty obviously a Master-Detail. We can’t buy wine without it existing and if I’m going to log a purchase I want the wine to be in the system as well. They are different objects because they have different attributes.
- Wines will have things like grape, year, tasting notes, aging notes, country of origin, etc
- Wine Purchases will inherit most of that information from the Wine, but also layer on things like Price, Quantity, Date of Purchase, and will have calculated fields for Remaining Quantity, when it should be consumed by, etc.
Wine Purchases to Consumed Bottles
Don’t really need to explain this one. Drinking is pretty boring if there isn’t a wine to drink
Our Cellar to Wine Purchases
I went back and forth on whether I needed a cellar object as well as Wine Purchases and ended up in the yes camp. Here are a few reasons why
- Over time I want to track things like average temperature and humidity
- I want to Roll-Up metrics like total bottles, red vs white, average cost, total worth (potentially for insurance)
- I feel like there will be other reasons that I haven’t even thought of yet.
So this is a Master-Detail as well I think vs a Lookup and that’s primarily because I think it makes more sense for my reporting to be “My Cellar with Wine Purchases” versus “Wine Purchases with My Cellar”.
Sometimes just sounding out what the standard report type will be can help determine which may makes more sense for a relationship to go