Shouldn't primary key of categories spreadsheet be category and group?

Google Sheets
1

for example, if one owns multiple rental properties:

cat group
insurance property 1
gas property 1
insurance property 2
gas property 2

I know there’s a seperate template for rentals properties but I’m just using this as an example.

best,

2

Welcome, @quietdesperation! :wave:

Categories should be unique so you’d want to prepend or append the category with a property “key”

E.g.

Category | group
Prop1-insurance | prop 1
Prop1-gas | prop 1
Prop2-insurance | prop 2
Prop2-gas | prop 2

Hope that helps!

Heather

3

thanks, yes, that’s what I did but it suffers from the expected negatives of denormalization one of which is having to repeat the property number even though it’s specified in the group. And of course, if I change the property name I have to change it in two places. I guess whoever created the template knew this and thought it through.

best,

4

Hi @quietdesperation - that’s right, the design was intentional. There is a whole lot more of a mess created if you don’t have unique categories :slight_smile:

5

@quietdesperation - As you noticed in other threads, I, too, am curious about differences between spreadsheets and databases.

In general, I have found that Tiller is not as rigorous on being a commercial Data Base (e.g. 5th Normal Form), but rather is aimed at a consumer tool. I’ve found many instances that feel more denormalized for easy query (e.g. all the week- and month-centric variants of transaction date).

Specifically to this point, I have looked at “Category” as a convenient reporting field rather than part of a compound index. I even took it a step further and added a “Tax Category” field. I use “Category” for budgeting and am hoping “Tax Category” will be useful in a few months.

6

I hope we are striking the right balance, @michaelrwolfseattle. One of the great things about spreadsheets is that it is so easy to build on the foundation of a template to add & personalize the spreadsheet to your needs— like adding a Tax Category field. :wink:

7

quietdesperation

My use case for Tiller sounds similar to yours. Generally, we use Tiller to simplify the process of collecting data needed for tax preparation.

After using Tiller for about 2 months, and after several restructures of Categories, Groups and Tags, this is how we structure our transactions to generate the following reports:

  • Tax-related transactions for our 2 rental properties.
  • Other Deductible Expenses for tax reporting
  • Other Spending and Cash Flow reports organized into Groups: Fixed, Semi-Fixed and Discretionary

We have 20 Categories for each rental property just as you describe. e.g. 731 Advertising, 731 Auto & Travel, 731 Capital Improvements, etc. We repeat the same categories for our other rental property.

  • All Rental expenses are assigned to either Group: 731 Expense or 744 Expense
  • All Rental Income is assigned to either Group: 731 Income or 744 Income
  • All 731 and 744 Income and Expenses are also assigned to Tag, “Rentals”

As you said, having the property address in the category and group isn’t the ideal data structure. A Query function could pull all transactions with a category containing either 731 or 744. This would generate similar reports but, so far, I haven’t seen a real benefit compared to a pivot table using Slicers and other filtering built into Sheets > Pivot Table. When necessary, changing how things are categorized or Groups in Tiller is pretty easy.

We have 86 other categories for non-rental expenses. Thanks to Tiller’s AutoCat feature, this isn’t as bad as it sounds. Without AutoCat, I’m sure we wouldn’t use so many Categories.

Categories for non-rental expenses are assigned to Groups as shown below:

  • Deductible Discretionary Expense
  • Deductible Fixed Expense
  • Deductible: Semi-Fixed Expense
  • Expense: Discretionary
  • Expense: Fixed
  • Expense: Semi-Fixed

The distinction between Discretionary, Fixed, and Semi-Fixed is subjective but close enough for our purposes.

Incomes are categorized by Source.

In addition, we use tags on some of these expenses to track things like Extraordinary expenses that we don’t think will recur every year or Subscriptions just because I want to know how much we’re spending on Subscriptions. We also use tags as Randy has suggested as a higher level of categorization to group expenses that are in different categories such as fuel, hotels, and restaurants that are all related to a specific Vacation (tag).

Again, thanks to AutoCat, most of the transactions are automatically categorized when imported.

With this structure, I’ve been able to create the reports I want for tax filing using pivot tables and / or some of the reports built into Tiller.

That said, I wouldn’t be surprised if someone had a better way and I’d be interested in learning better ways of using Tiller. I think it’s a fantastic application and their support is terrific.

I’d also be interested in learning more about how michaelworlfseattle added the Tax Category field and how he uses it. Is that any more complicated than just adding another column in Transactions and possibly AutoCat? From what I understand, we can safely add columns to Transactions as long as we don’t change the default column headings from the basic template.

1 Like