My name is Jake Marwil and I am a Customer Support Advocate here at TrackVia. Our customers commonly call in to ask about the structure of their tables and if there is a better way to build them. Each customer is going to employ a different structure depending on their data and workflow, but there are a few best practices when it comes to building tables. Here are my top five suggestions on optimizing tables in your TrackVia app.
1. Remove Redundancy / Normalize
When it comes to optimizing tables in relational databases, a common goal is to remove any sort of redundancy in the tables. This means you should strive to have a table with unique values (which will serve as a parent table) and then a table with multiple entries (the child table) which will be connected to a single record in the parent. Without normalization, database systems can be inaccurate, slow, and inefficient, and might not produce the data you expect.
For example, a customer may be building a database that keeps track of companies and contacts at each company. In Excel, you may have columns for both the company and company details as well as the contact and contact details. This implies you would have a row for each contact, meaning there would be rows where a company may be repeated. Within TrackVia, it would be best to split up this single table into two tables, one for Companies (the parent) and one for Contacts (the child).
2. Choose Proper Field Types
Planning what field types will be most relevant to your data can save you time down the road. It is important to consider what type of data you will be storing in your tables. If you have a phone number field, would a short answer field or a number field work better for you? The short answer field will allow all characters so that you can format the phone number field to your desired format, e.g. “(303) 123-1234”. However, if you have many users entering data into your table, there is a chance they will mistype the phone number or use undesired formatting. To mitigate this, you may want to consider using a number field, which only allows the input of numbers. This can cut down on user input error when optimizing tables.
If you have users entering information that requires a constant value, such as a status field being “Complete” or “Incomplete”, a drop down may be better than a short answer field. The drop down will only allow one pre-determined choice and eliminate any chance of a user entering an undesired selection and helps maintain data consistency.
3. Order Your Fields
When optimizing tables it is important to see your data in a specific order. It wouldn’t make much sense to have a companies table where the phone number and address fields were listed first and the name of the company last, would it? You wouldn’t be sure which company you were looking at until you scrolled all the way to the right to check.
Rather than taking the extra effort to scroll, it would be best to restructure the order of fields in the table builder. In your default view, the field listed on the top of the ‘Included Fields’ list is going to appear as the far left field in your table grid view. You can easily drag and drop fields on top of or below each other to rearrange them in your default view.
If you are creating a new view, then you have the option to reorder your fields using the Format tab.
4. Choose A Relevant Record ID
Selecting a relevant Record ID will help when linking records from child tables to a parent for optimizing tables in TrackVia.
If I am trying to connect a person in my contact table to the company table, it wouldn’t make much sense if the drop down to select the proper record in the parent table consisted of phone numbers, would it? Users wouldn’t necessarily be able to recognize which phone belongs to which company.
If, instead, we switch the Record ID in the Company table to be the Company Name field, this would be a lot easier to understand. I know that the contact record I am working on works at TrackVia, for example, so I would be able to select ‘TrackVia’ from the drop down to link. Remember, you have the ability to use multiple fields as your Record ID, so consider what information is most relevant to your data. A date field combined with a description of an item may help you and your users recognize a proper connection.
5. Include Parent Fields
Sometimes you may want to view information in a child table which exists in a parent table. This is possible through the use of a parent look-up function and a calculated field. However, rather than going through the process of editing your table structure and writing a formula, you can easily create a view and choose to include fields from any parent table by simply selecting them and clicking Add to include them in the Included Fields. This cuts down on the time spent having to write a formula and allows you to easily remove fields from your view at any time rather than having them to delete them in the Table Builder.
I hope this helps you to structure your tables in the most efficient way for your data. If you have any other questions regarding optimizing tables, please feel free to comment below or refer to our knowledge base. You can also reach out to us on Twitter.