The Journey of a New Quickbase Builder: Tables and Relationships
My first attempt at app planning and building my first data model was not as successful as I would have liked. It took me 5-10 tries to create and relate my tables correctly, and with each failed attempt I was getting increasingly frustrated... “Why is this not working?!” Finally, it clicked. It took my many failures in trying to connect my data for me to begin understanding when to create tables and how Quickbase relationships work. Tables and relationships can cause so much overthinking when in fact they can be very simple to understand.
Creating relationships is the foundation of app building. Relationships are built in a one-to-many matter meaning one record in the Master table can relate to many records in the Details table. This will reduce database sizes, data entry, errors, and it enables workflow automation. Figuring out the desired workflow will help you decide how relationships should be created.
My “Aha” moments for tables and relationships:
- Tables are created when you need to track many of a certain object and the object has several data points. In simpler terms these tables are going to be nouns you want to track.
Example: Track Many Meetings, Expenses, and Invoices that each have several data points.
Example of when not to create a table: if I were tracking the electronics and their life span in my household, I would not want to create a table for each electronic, right? Refrigerator table, TV table, Toaster table etc. That wouldn’t make sense. There aren’t many of each table type, nor several data points to track about them. Who has multiple toasters? No one is the answer. The best way would be to create an Electronics table and add all my electronics under that one common table.
- Relationships are a connection between two tables that allow tables to share information with one another. It is important to understand that relationships work as a one-to-many (Parent to Child). As seen on my lucid chart video I created an app for a bakery in which I created a relationship where One Customer has Many orders. Understanding the one-to-many concept made my life easier in understanding relationships and when to create them.
Visual Quickbase example:
The picture above shows a visual example of what happens when tables are connected In Quickbase. Candidates have many Interviews, as you can see interviews references the candidates by their application ID and the interviews information is sent up to the (parent) Candidates table. The add child button is automatically created in the relationship so under the candidate you can add an interview instead of going to the interviews table. The report link is also automatically created that shows all the interviews of that candidate. Essentially all information can be entered into the Candidates table.
Note: An Interview can also have many Candidates. But I’m not blogging about many-to-many relationships yet, that will come later…Quit skipping ahead!
Final piece of Advice: There is much to learn about tables and relationships, what I have given is a high level of my key understandings that will hopefully get you going in the right direction. What has helped me and continues to help me understand tables and relationships is making them and making mistakes. The more you create the more you will understand. Create Create Create! I would highly recommend using our free builder accounts, visiting our exchange library and viewing/understanding different structures of applications, and of course, making use of our QB university App Building courses.
As always please feel free to leave a comment!