So I know that when someone asks about how to combine two tables into one, usually the answer is “You should instead first create a master table, and then split it into multiple views.” But what about when the tables I want to combine have very different schemas?
Here’s a simplified version of my problem:
- I have a Donations table
- I have an Expenses table
I want to track how we are doing, over time, in terms of both donations and expenses for each budget category every month and every year. I could combine donations and expenses into one table (eg, “Transactions”) but then I would have so many empty columns in each (and the more complex version has more tables that I want to combine).
I am leaning towards a different option right now: make a button that pulls in rows from Donations and Expenses into a third table (eg, “Budget Lines”) and then integrate only that one with my Budget table for analysis and charts and whatnot.
Any tips from the community? (I’m also not a finance person, so maybe I may very well be missing something obvious from that area of expertise.)
Hello @Ryan_Martens2 ,
There is no simple answer to your question. In order to see how you are doing you don’t have to pull rows to a 3rd table, you need to work with the data from the 2 separate tables (if indeed you really need 2 master tables). What is the scale of your operation - i.e. how many entries are you expecting per month/year/decade? Empty columns are not always a problem - and you probably don’t need that many empty columns, because we are on both ends talking financial data, so there should be more the same than different. In your output (what you call 3rd table), do you need details on each transaction, or the results?
You have already been going through the postings on the community, so you probably know what is next: make and share a simplified doc (without sensitive data) and share it with the community in order to get some useful feedback.
Greetings,
Joost
2 Likes
Hi @Ryan_Martens2
Whatever approach you decide to take, I recommend creating a quick prototype with fake data that specs out the core workflow of entering and working with the views. You will quickly find pros and cons to different approaches in how many clicks are needed to enter things, and impacts to the different available view layouts.
You may want to compare a couple different approaches. E.g.
- A
Tansactions table that contains all core transaction data and then two Donation and Expenses table that lookup into the Transactions and extend the core data with context-specific info.
- The completely separate tables you suggested that somehow combine into a composite table as needed.
3 Likes
I’ll just up @Ed_Liveikis 's words because I was going to suggest just that.
Very often you’ll see which schema works best for you after you actually start building an interface for working with that data. And yeah, I’d go with the “common Transactions table, separate Donations/Expenses tables that have a Transaction column”. The downside here is that you’d have to create two rows: for each Donation/Expense also create a Transaction row, and for that you’d have to build some external UI, e.g. a form-like layout based on a helper table with a “Save” button that would create the rows behind the scenes. I.e. I mean you won’t be able to just click add row on a table and expect another row to appear on another table, that’s the downside of such approach.